Business Intelligence Case

Power BI for public sector decision-making

An executive BI simulation with fiscal KPIs, data modeling, ETL and automatic insights.

  • Power BI
  • DAX
  • Power Query
  • SQL
  • ETL
  • KPIs
  • Public Management
  • Data Analytics
Collected revenue (2025, simulated)
R$139.6M
Target attainment
98.9%
Tributes monitored
7
View dashboardView data modelView projectsGet in touch

A Business Intelligence simulation for public management, based on real revenue-analysis routines. It shows how I structure an executive dashboard: data preparation, modeling, KPIs and visual communication.

Simulated data for technical demonstration. No sensitive fiscal information is used.

Simulated executive dashboard

KPIs, trend and ranking react to the year, period and tribute filters — the same mechanics of a published Power BI report, implemented natively for the web.

Estimated revenue
R$141.1M
Collected revenue
R$139.6M
Target attainment
98.9%
Accumulated gap
−R$1.5M
Monthly growth
−6.9%
Tributes under watch
ISS (services tax), Overdue debt, Other revenue, Fees, Asset revenue
Overall status
Healthy

Monthly trend

Trend chart of simulated monthly revenue — All tributes. Solid line: collected; dashed line: estimated.

View trend data as a table
MonthEstimatedCollected
Jan/2025R$14.2MR$14.1M
Feb/2025R$13.7MR$13.8M
Mar/2025R$13MR$13M
Apr/2025R$12MR$12.1M
May/2025R$11.5MR$11.5M
Jun/2025R$11.2MR$11.2M
Jul/2025R$10.8MR$10.6M
Aug/2025R$10.7MR$10.4M
Sep/2025R$11.1MR$10.8M
Oct/2025R$11.2MR$11.4M
Nov/2025R$11.1MR$10.8M
Dec/2025R$10.5MR$10M

Ranking by tribute

Bars compare accumulated revenue per tribute in the filtered window; the percentage shows target attainment.

  1. IPTU (property tax)R$50.6M100.4%
  2. ISS (services tax)R$41.1M95.0%
  3. ITBI (transfer tax)R$17.4M103.5%
  4. Overdue debtR$11.2M99.3%
  5. Other revenueR$8.6M99.8%
  6. FeesR$6.2M99.8%
  7. Asset revenueR$4.6M99.9%

Automatic insights

  • Performance within the expected band, with stable monthly tracking.
  • ISS (services tax) under target for 8 consecutive months.
  • ITBI (transfer tax) with atypical positive variation in Oct/2025 (34% above the estimate).
  • IPTU (property tax) concentrates 36.2% of revenue in the period.
  • Asset revenue with low share of the accumulated total (3.3%).
  • Accumulated gap concentrated in ISS (services tax) (95.2% of the total deviation).

Generated by deterministic rules over the filtered data — the same logic covered by unit tests.

Simulated data for technical demonstration. No sensitive fiscal information is used.

Data model

Simplified star schema designed to organize measures, filters and accumulated analysis.

Fact tableFatoArrecadacao

  • ValorArrecadado
  • ValorEstimado
  • DataId
  • TributoId
  • MetaId

DimensionsDimTempo

  • Data
  • Mês
  • Ano

DimensionsDimTributo

  • Tributo
  • CategoriaId

DimensionsDimCategoria

  • Categoria

DimensionsDimMeta

  • MetaMensal
  • MetaAnual

DimensionsDimStatus

  • Faixa
  • Limite

Relationships

  • FatoArrecadacao → DimTempo
  • FatoArrecadacao → DimTributo
  • DimTributo → DimCategoria
  • FatoArrecadacao → DimMeta

ETL pipeline

Simulated flow inspired by Power Query, SQL and Python routines to turn fiscal bases into reliable information.

  1. 01Data collection

    Simulated fiscal bases and source spreadsheets

  2. 02Standardization

    Names, types and formats unified in Power Query

  3. 03Cleansing

    Deduplication and consistency validation

  4. 04Modeling

    Fact + dimensions in a star schema

  5. 05KPI calculation

    DAX measures for attainment, gap and YTD

  6. 06Executive visualization

    Dashboard with filters and status

  7. 07Insights

    Automatic alert and recommendation rules

Conceptual DAX measures

The measures below are conceptual — they demonstrate the analytical reasoning behind each dashboard indicator.

Collected Revenue

Receita Arrecadada =
SUM(FatoArrecadacao[ValorArrecadado])
Purpose
Consolidates the amount actually collected under the current filter context.
How it supports decisions
The base measure of the whole tracking — without it there is no target comparison.

Estimated Revenue

Receita Estimada =
SUM(FatoArrecadacao[ValorEstimado])
Purpose
Forecast total for the same context, sourced from the target dimension.
How it supports decisions
Sets the yardstick: every executive reading is 'actual versus forecast'.

Attainment %

Atingimento % =
DIVIDE([Receita Arrecadada], [Receita Estimada])
Purpose
Target attainment percentage with safe division (DIVIDE avoids divide-by-zero).
How it supports decisions
Feeds the status bands (critical, attention, healthy, above target).

Gap

Gap =
[Receita Arrecadada] - [Receita Estimada]
Purpose
Absolute deviation between actual and forecast.
How it supports decisions
Shows where to focus recovery effort — percentages hide magnitude.

Year-to-Date

Receita Acumulada =
CALCULATE(
  [Receita Arrecadada],
  DATESYTD(DimTempo[Data])
)
Purpose
Year-to-date total using time intelligence over the calendar dimension.
How it supports decisions
Standard executive reading: managers decide on the accumulated view, not a single month.

Monthly Variation

Variação Mensal =
DIVIDE(
  [Receita Arrecadada] - [Receita Mês Anterior],
  [Receita Mês Anterior]
)
Purpose
Growth against the previous month, the basis for trend alerts.
How it supports decisions
Flags inflections early — three consecutive drops raise an alert before quarter end.

Before and after

Before

  • Isolated spreadsheets
  • Low executive visibility
  • Manual indicators
  • Recurring rework
  • Hard to spot deviations
  • Fragmented communication

After

  • Executive dashboard
  • Standardized KPIs
  • Filters by period and tribute
  • Visual alerts
  • Automatic insights
  • Decision support

Technical decisions

Why simulated data?

To demonstrate the analytical logic without exposing sensitive information.

Why a native dashboard in the portfolio?

To guarantee performance, SEO, visual control and interaction without relying on an external embed.

Why separate insight rules?

To keep the analytical logic testable, reusable and independent of the visual layer.

Why a star schema?

To organize filters, dimensions and measures in a scalable way.

Why not embed Power BI directly?

Because this page's goal is to demonstrate BI reasoning and architecture in a public, controlled and performant web environment.

Stack

Power BIDAXPower QuerySQLPythonETLData ModelingNext.jsTypeScriptTailwind CSSVercel

From ETL pipeline to dashboard

I structure indicators, the data model and Power BI visualization for how your team works.

Get in touchView projects