Jhonatan Pinheiro
Loading page...
Jhonatan Pinheiro
Loading page...
Jhonatan Pinheiro
Loading page...
75 topics following one real retail case end to end: Power Query, star schema, the SQL behind the model, DAX from CALCULATE to time intelligence, dashboard design, Figma and usage metrics.
A beautiful dashboard nobody uses is a cost. An ugly dashboard that answers the right question is useful — but it does not last, because nobody defends what is a chore to look at. This guide brings together the four skills that make an analytical product work: SQL (where the data comes from), modelling + DAX (what the number means), Power BI Desktop (where it becomes a product) and UX/UI (why someone comes back tomorrow).
Every example uses the same company and the same numbers, from the SELECT to the layout:
Casa & Construção Nordeste — 42 stores across 6 states, 3 channels (physical store, e-commerce, telesales), around 4.2 million sales rows a year on a SQL Server. The board wants to answer three questions: are we hitting the margin target?, which stores fell against last year? and where is stockout eating into revenue?
The model: fVendas (fact, grain = receipt line item), dProduto, dLoja, dCliente, dCalendario and fMetas.
How to read this: if you have never opened Power BI, go from 1 to 75 in order. If you already deliver reports, start at 21 — modelling is where most of the problems that show up disguised as "a DAX error" are born.
Before the tool: what you are building, for whom, and what decision it changes.
A report delivers rows for checking (the tax analyst wants invoice 4471). A dashboard monitors a small, stable set of indicators to trigger action. An ad hoc analysis answers a one-off question and dies afterwards. Mixing all three on the same screen is the number-one cause of a slow, confusing dashboard: the director opens it to look at the margin and takes 40 thousand rows along.
Board -> dashboard -> 6 indicators, 1 screen, 3 s to load
Manager -> dashboard -> the same screen + drill by store
Analyst -> ad hoc -> Analyze in Excel / live connection
Tax -> report -> paginated (Report Builder), exportableBefore choosing a visual, write the sentence: who decides what, how often, and what changes when the number is bad. If there is no action attached, the indicator is curiosity — and curiosity does not deserve space on the main screen.
Indicator card (fill this in BEFORE building)
Decision: reallocate media budget between channels
Who decides: trade marketing manager
Frequency: every Monday, 9 a.m.
Trigger: channel margin < 22% for 2 weeks running
Action: cut the channel's budget and move it to the highest-margin oneEvery dashboard has four layers, and each problem lives in one of them. Diagnosing in the wrong layer costs weeks: people rewriting DAX when the problem is the fact's grain, or changing colours when the problem is that the number does not answer the question.
1. Source SQL Server, spreadsheets, API -> reliability
2. Model star schema, relationships -> performance and truth
3. Semantics DAX measures, formatting -> meaning
4. Interface layout, colour, interaction -> adoptionA metric is what you add up (revenue, units). A dimension is the slice (store, product, date). Granularity is the smallest row of the fact — in our case, a receipt line item. You can never go below the grain you loaded: if the fact arrives aggregated by day and store, the question which product drove the drop is unanswerable forever.
fVendas (grain = receipt line item)
+------------+--------+---------+-----+--------+-------+
| DataVenda | LojaSK | ProdSK | Qtd | Valor | Custo |
+------------+--------+---------+-----+--------+-------+
| 2026-03-14 | 17 | 90421 | 2 | 179,80 | 118,40|
Aggregating later is easy. Disaggregating is impossible.If three departments calculate revenue three different ways, the dashboard becomes a stage for argument instead of decision. Write down the definition, the formula and the exceptions — and put that inside the report itself, on a glossary page. It is the cheapest item to produce and the one that prevents the most meetings.
Net Revenue
Definition: gross sales - returns - sales taxes
Source: fVendas.ValorLiquido (already net of tax at source)
Excludes: test sales (LojaSK = 999) and zero-value exchanges
Owner: Controlling
Refreshes: daily at 6 a.m. (D-1)The navigation basics and the configuration decisions that save you months later.
Report is where you draw. Table (formerly Data) is where you check what arrived. Model is where you link the tables. A beginner spends 100% of their time in the first and takes weeks to discover the problem was in the third.
Left-hand sidebar:
[ ] Report -> visuals, layout, formatting
[#] Table -> check values, create a calculated column
[<>] Model -> relationships, cardinality, hide columnsImport copies the data into the in-memory model (VertiPaq): fast and with all of DAX available — it is the right default in 90% of cases. DirectQuery queries the source on every click: live data, but slow and with limited DAX. Dual lets the engine choose, in composite models. Our 4.2-million-row fact fits into Import with no drama (~120 MB compressed).
Import -> up to hundreds of millions of rows, scheduled refresh
DirectQuery -> a real-time requirement (< 5 min) or data that cannot leave the source
Dual -> small dimensions in a composite model
Rule of thumb: only use DirectQuery when someone proves,
in writing, that a 1-hour refresh does not solve it.The wizard invites you to tick the table and click Load — and then you bring 60 columns and 12 years of history to answer about 24 months. Connect through a view or a filtered query. Fewer columns means less memory, a shorter refresh and a more readable model.
Get Data > SQL Server
Server: srv-bi.casaeconstrucao.local
Database: DW_Vendas
Mode: Import
Instead of ticking the whole fVendas table:
SELECT ... FROM dbo.vw_fato_vendas WHERE DataVenda >= '2024-01-01'
Bring only the period the dashboard shows (+1 year, for the comparison).A hard-coded server and database mean redoing everything when promoting from staging to production. Create parameters in Power Query and reference them in the source: switching environments becomes changing two fields — and in the Service, it is a parameter setting, with no republishing.
let
Fonte = Sql.Database(pServidor, pBanco),
Vendas = Fonte{[Schema="dbo", Item="vw_fato_vendas"]}[Data]
in
VendasBy default Power BI creates a hidden date table for each date column in the model. With 8 date columns, that is 8 invisible tables, wasted memory and unpredictable time intelligence. Turn it off and use a single dCalendario of your own.
File > Options and settings > Options
> Current File > Data Load
[ ] Auto date/time <- UNTICK
[ ] Autodetect new relationships... <- UNTICK (create them yourself)
[ ] Update data when opening the file <- assess case by caseIn Desktop, Refresh reloads everything now. In production the Power BI Service does the refreshing, with its own credential and, for an on-premises source, a gateway installed on the company's network. The classic failure: it works on your laptop and breaks in the Service, because the SQL Server is not visible from outside.
Desktop -> Home > Refresh (uses your Windows credential)
Service -> Semantic model settings > Scheduled refresh
+ On-premises data gateway (on-premises source)
+ Service credential (never your personal one)
Limit: 8 refreshes/day (Pro), 48 (Premium/Fabric).The .pbix is a binary package — in Git, every commit becomes a new blob and a diff is impossible. The .pbip format (Power BI Project) writes the model and the report as text files, so you can review a measure change in a pull request, like code.
File > Save as > Power BI project (.pbip)
meu-painel.pbip
meu-painel.Dataset/
model.bim <- tables, relationships, measures (text)
meu-painel.Report/
report.json <- pages, visuals, layout (text)
And then: git diff shows "measure Margem % changed from X to Y".Where dirty data becomes a trustworthy table — and where many people do the work SQL would do better.
The rule of as close to the source as possible: filter, join and aggregate in SQL (the server was built for it); shape the format and typing in Power Query; calculate an indicator that reacts to filters in DAX. Summing in Power Query what should be a measure creates a number that does not respond to slicing — and everyone concludes the dashboard is wrong.
SQL -> JOIN, WHERE, heavy GROUP BY, history
Power Query -> types, unpivot, light merge, fixed derived columns
DAX -> everything that changes with the user's filter
A warning sign: a calculated column called "Total for the Year".
It does not change when the user filters. It should be a measure.A file generated in pt-BR uses a decimal comma; interpreted as en-US, 179,80 becomes 17980 — with no visible error, just revenue a hundred times higher. Always type explicitly, stating the source culture.
= Table.TransformColumnTypes(
Fonte,
{{"ValorLiquido", type number}, {"DataVenda", type date}},
"pt-BR"
)The first useful step of nearly any query is throwing away what will not be used: less memory, a shorter refresh and a better chance of query folding. Prefer Table.SelectColumns (which lists what stays) to removing columns — if the source gains a new column, your query does not break.
= Table.SelectColumns(
Fonte,
{"DataVenda", "LojaSK", "ProdutoSK", "Qtd", "ValorLiquido", "Custo"}
)Folding is Power Query translating your steps into SQL and getting the server to run them. While it is happening, a date filter costs almost nothing; when it breaks, Power BI downloads everything and filters on your machine. Things that break folding: Table.Buffer, M functions with no SQL equivalent and complex custom columns.
Right-click on the step > "View Native Query"
enabled -> the step is still being translated into SQL
greyed -> folding broke from here on
Strategy: do ALL the steps that fold first,
and only afterwards the ones that break it.A targets spreadsheet nearly always comes wide (Jan, Feb, Mar as columns). The model needs it tall: one row per store and month. Unpivot Columns solves it — and using Unpivot Other Columns means a new month in the spreadsheet does not break the query.
// Before: Loja | Jan | Fev | Mar
// After: Loja | Mes | MetaValor
= Table.UnpivotOtherColumns(
Fonte,
{"Loja"},
"Mes",
"MetaValor"
)Append stacks tables with the same columns (2025 sales + 2026 sales). Merge is the JOIN: it brings columns from another table by the key. In a star schema, merging is nearly always a mistake — the link should be a relationship, not a column copied into the fact.
Append -> same columns, more rows
Merge -> same rows, more columns
When merging is right:
joining a dimension that arrived split across two sources
When it is wrong:
copying dProduto[Categoria] into fVendas "to make things easier"Each store sends an inventory Excel file. Instead of 42 identical queries, create a function that takes the binary file and returns the cleaned table, and apply it over the whole folder. When the rule changes, you change it in one place.
// fxLimpaInventario
(arquivo as binary) as table =>
let
Planilha = Excel.Workbook(arquivo){[Item="Inventario"]}[Data],
Cabecalho = Table.PromoteHeaders(Planilha),
Tipado = Table.TransformColumnTypes(
Cabecalho, {{"SKU", type text}, {"Saldo", Int64.Type}}, "pt-BR"),
SemVazio = Table.SelectRows(Tipado, each [SKU] <> null)
in
SemVazioFixed bands and classifications (ones that do not depend on filters) belong in Power Query. And always decide what to do with a conversion error: turning it into null is honest; breaking the refresh is worse; hiding it with try otherwise 0 without telling anyone is the worst of all, because it makes the problem disappear and keeps the wrong number.
= Table.AddColumn(Fonte, "FaixaTicket", each
if [ValorLiquido] >= 500 then "Alto"
else if [ValorLiquido] >= 150 then "Medio"
else "Baixo", type text)
// a controlled error, flagged so it can be audited later
= Table.AddColumn(Fonte, "CustoTratado", each
try Number.From([Custo]) otherwise null, type nullable number)This is where nearly every problem is born that later shows up disguised as a DAX error or as slowness.
One giant table with everything inside looks simple and is a trap: the product's category repeats 4.2 million times, the filters get slow and there is no way to list a product that did not sell. In a star schema, the fact holds numbers and keys; the dimensions hold descriptions. It is the shape Power BI's engine was built for.
dCalendario
|
dLoja -- fVendas -- dProduto
|
dCliente
fact = numbers + keys (narrow and long)
dimension = text + hierarchies (wide and short)If the column answers how much and it makes sense to add it up, it is a fact. If it answers who, what, where, when and you want to filter or group by it, it is a dimension. A quick test: adding up postcodes means nothing — a postcode is a dimension, even though it is numeric.
fVendas : Qtd, ValorLiquido, Custo, Desconto (addable)
dProduto : SKU, Description, Category, Brand (groupable)
dLoja : Name, City, State, Region, Format
dCalendario: Date, Year, MonthName, Quarter, WeekdayThe healthy default is one-to-many (1:*) with a single-direction filter, from the dimension to the fact. Many-to-many and bidirectional filtering solve specific cases, but they create ambiguity and slowness — and they are the most common explanation for a total that does not match the sum of its parts.
dLoja[LojaSK] 1 ---- * fVendas[LojaSK] direction: single (dLoja -> fVendas)
Bidirectional cross-filtering: use it only when you need
to filter the dimension by the fact — and prefer solving that
with CROSSFILTER inside the measure, not in the relationship.Time intelligence (TOTALYTD, SAMEPERIODLASTYEAR) requires a continuous date table, with no gaps, covering whole years and marked as a date table. Without that, the comparison against the previous year silently returns wrong results at the edges of the period.
dCalendario =
VAR MinData = MIN( fVendas[DataVenda] )
VAR MaxData = MAX( fVendas[DataVenda] )
RETURN
ADDCOLUMNS(
CALENDAR( DATE( YEAR(MinData), 1, 1 ), DATE( YEAR(MaxData), 12, 31 ) ),
"Ano", YEAR([Date]),
"MesNum", MONTH([Date]),
"MesNome", FORMAT([Date], "mmm"),
"AnoMes", FORMAT([Date], "yyyy-mm"),
"Trimestre", "T" & QUARTER([Date]),
"DiaSemana", FORMAT([Date], "ddd")
)
// Then: Table tools > Mark as date table > Date columnWithout configuring it, the axis shows Apr, Aug, Dec, Feb... Select the text column and use Sort by column, pointing at the month number. It applies to any text with an order of its own: ticket band, store size, funnel stage.
dCalendario > MesNome column
> Column tools > Sort by column > MesNum
Careful: the sorting column must have exactly
1 value per label — otherwise Power BI refuses.The fact has an order date and a delivery date. Only one relationship can be active at a time with dCalendario; the other stays inactive and is switched on inside the measure. That is how you show revenue by sale date and, beside it, deliveries by delivery date — on the same axis.
Entregas =
CALCULATE(
[Faturamento],
USERELATIONSHIP( fVendas[DataEntrega], dCalendario[Date] )
)Not every table needs a relationship. A table with no link at all lets the user pick a value (an adjustment percentage, a scenario) that the measure reads with SELECTEDVALUE. It is the basis of any simulation in Power BI.
// Modeling > New parameter > Numeric
ReajustePreco = GENERATESERIES( 0, 0.20, 0.01 )
Faturamento Simulado =
VAR Reajuste = SELECTEDVALUE( ReajustePreco[Valor], 0 )
RETURN
[Faturamento] * ( 1 + Reajuste )Whoever uses the dashboard should not see LojaSK, ProdutoSK or the fact's raw columns. Hide the keys, hide what has already become a measure, and group the measures into display folders. A model with 12 visible items is usable; one with 140 pushes everyone back to Excel.
Model view > select the column > Properties
Is hidden: Yes
Measure display folders:
Sales/ Faturamento, Ticket Médio, Unidades
Profitability/ Margem R$, Margem %
Comparisons/ Faturamento AA, Var % AA, YTD
Targets/ Meta, Atingimento %The layer nobody sees and which decides whether the dashboard refreshes in 2 minutes or in 2 hours.
Do not point Power BI at physical tables. Create views with business names, columns already renamed and rules applied (excluding the test store, for instance). When the physical table changes, you fix the view — and none of the 30 reports break.
CREATE VIEW dbo.vw_fato_vendas AS
SELECT
v.DataVenda,
v.LojaSK,
v.ProdutoSK,
v.ClienteSK,
v.Quantidade AS Qtd,
v.ValorLiquido,
v.CustoUnitario * v.Quantidade AS Custo
FROM dbo.FatoVendasItem v
WHERE v.LojaSK <> 999 -- test store
AND v.StatusCupom = 'FECHADO';The dashboard shows 24 months and compares against the previous year — so 36 months are enough. Cutting 9 years of history took the refresh from 38 minutes down to 6, and the model from 480 MB to 120 MB. Leave the full history in a separate report, for whoever genuinely needs it.
SELECT ...
FROM dbo.vw_fato_vendas
WHERE DataVenda >= DATEADD(
MONTH, -36,
DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
);If no page goes below day + store + category, aggregate at that grain in SQL. Our fact went from 4.2 million to 380 thousand rows and the dashboard became instantaneous. Only do this when you are sure the detail will not be asked for — disaggregating afterwards is impossible.
SELECT
v.DataVenda,
v.LojaSK,
p.Categoria,
SUM(v.Qtd) AS Qtd,
SUM(v.ValorLiquido) AS ValorLiquido,
SUM(v.Custo) AS Custo
FROM dbo.vw_fato_vendas v
JOIN dbo.dProduto p ON p.ProdutoSK = v.ProdutoSK
GROUP BY v.DataVenda, v.LojaSK, p.Categoria;ROW_NUMBER, RANK and SUM() OVER solve on the server what would be expensive in DAX over a large table. Especially useful for historical snapshots — each month's frozen ranking, which should not change when the user filters.
SELECT
LojaSK,
AnoMes,
Faturamento,
RANK() OVER (PARTITION BY AnoMes ORDER BY Faturamento DESC) AS PosicaoMes,
SUM(Faturamento) OVER (PARTITION BY LojaSK, LEFT(AnoMes, 4)
ORDER BY AnoMes ROWS UNBOUNDED PRECEDING) AS AcumuladoAno
FROM dbo.vw_vendas_mes_loja;Incremental refresh makes Power BI reload only the recent window (the last 10 days, for instance) and keep the rest. It requires a date/time column at the source and the reserved parameters RangeStart and RangeEnd — with those exact names — used as a filter in the query.
// Power Query: RangeStart and RangeEnd parameters (Date/Time type)
= Table.SelectRows(Fonte, each
[DataVenda] >= RangeStart and [DataVenda] < RangeEnd)
// Modeling > Incremental refresh:
// Archive data starting 3 years before refresh date
// Incrementally refresh data starting 10 days before refresh dateThe refresh query is always the same: a date filter and a projection of a few columns. An index on the sale date, with the used columns included, avoids scanning the whole table. Measure before and after — in our case, 4m12s became 47s.
CREATE NONCLUSTERED INDEX IX_FatoVendas_Data
ON dbo.FatoVendasItem (DataVenda)
INCLUDE (LojaSK, ProdutoSK, Quantidade, ValorLiquido, CustoUnitario);
-- Check the plan before and after:
SET STATISTICS IO, TIME ON;A null that reaches the model becomes (Blank) in the visual, and a (Blank) in a dimension creates that phantom category nobody can explain in a meeting. Handle it in the view, with an explicit label — the user needs to know that sales without a category exist, not watch them vanish.
SELECT
p.ProdutoSK,
COALESCE(NULLIF(LTRIM(RTRIM(p.Categoria)), ''), 'Sem categoria') AS Categoria,
CAST(p.PrecoLista AS DECIMAL(10,2)) AS PrecoLista
FROM dbo.dProduto p;If the calculation does not change with whatever the user clicks, do it in SQL: it is faster, versioned and testable. If it does change with the filter (share of the total, comparison against the selected period), it has to be DAX — that is exactly what the language exists for.
SQL: margin per sales line item (fixed per row)
SQL: the product's ABC classification (recalculated in the ETL)
DAX: % of revenue under the current filter (changes with every click)
DAX: variation against the previous year (depends on the chosen period)DAX is not an Excel formula. The syntax is learned in an afternoon; the evaluation context takes a few months.
A calculated column is computed at refresh time, takes up memory and is fixed per row. A measure is computed on the fly, according to the visual's filter, and takes no memory. The default: use a measure. Use a column only when you need it to filter, to group or as a chart's axis.
// Column: exists per row, useful for filtering/grouping
fVendas[MargemLinha] = fVendas[ValorLiquido] - fVendas[Custo]
// Measure: reacts to the visual's filter
Margem R$ = SUM( fVendas[ValorLiquido] ) - SUM( fVendas[Custo] )Row context exists inside a calculated column or an iterator (SUMX): there is a current row. Filter context is the set of filters coming from the visual, the slicers and the relationships. Every measure is evaluated inside a filter context — understanding that is understanding DAX.
Visual: row = "Loja Recife", column = "March/2026"
That cell's filter context:
dLoja[Nome] = "Loja Recife"
dCalendario[AnoMes] = "2026-03"
+ whatever is in the slicers and page filters
The measure is executed once PER CELL, in that context.SUM adds up a column that already exists. SUMX walks the table row by row, evaluates an expression and adds the result. Whenever the calculation has to happen before the sum — price times quantity, for instance — the iterator is mandatory.
// Right: multiplies per row, then sums
Receita Bruta = SUMX( fVendas, fVendas[Qtd] * fVendas[PrecoUnitario] )
// Wrong: sums everything and multiplies at the end
Receita Errada = SUM( fVendas[Qtd] ) * SUM( fVendas[PrecoUnitario] )COUNTROWS counts the fact's rows (items sold). DISTINCTCOUNT counts distinct values (customers who bought). Careful: DISTINCTCOUNT is expensive on a high-cardinality column and is not additive — the sum of the months never equals the year, because whoever bought in January and in March is one person.
Itens Vendidos = COUNTROWS( fVendas )
Cupons = DISTINCTCOUNT( fVendas[CupomID] )
Clientes Ativos = DISTINCTCOUNT( fVendas[ClienteSK] )
// Jan: 1,200 | Feb: 1,350 | Total: 1,910
// It is not an error: it is the expected behaviour.Division by zero produces an error or infinity, and the visual shows something nobody understands. DIVIDE handles a zero or blank denominator and lets you choose the alternative result. Usually it is better to return blank than zero — a zero lies by claiming there was a sale with no margin.
Margem % = DIVIDE( [Margem R$], [Faturamento] ) // blank if there were no sales
Ticket Médio = DIVIDE( [Faturamento], [Cupons] )
Atingimento % = DIVIDE( [Faturamento], [Meta], 0 ) // here the zero makes senseCALCULATE evaluates an expression while modifying the filter context. It is the most important function in the language: comparisons, shares and scenarios all go through it. The filters passed in replace the existing filter on that column and keep the others.
Faturamento E-commerce =
CALCULATE( [Faturamento], dLoja[Canal] = "E-commerce" )
// Several filters = AND
Fat Ecom Nordeste =
CALCULATE(
[Faturamento],
dLoja[Canal] = "E-commerce",
dLoja[Regional] = "Nordeste"
)A simple filter (column = value) handles most cases. When the condition compares against a measure or involves more than one column, you need FILTER, which returns a table. Always filter the smallest possible table — using FILTER over the 4-million-row fact when the dimension would have done is a recipe for slowness.
// Receipts above R$ 500 (compares against the row's value)
Vendas Alto Ticket =
CALCULATE( [Faturamento], FILTER( fVendas, fVendas[ValorLiquido] > 500 ) )
// Better: filter the dimension, not the fact
Fat Categorias Premium =
CALCULATE( [Faturamento], FILTER( dProduto, dProduto[PrecoLista] > 500 ) )ALL/REMOVEFILTERS ignore filters and give you the grand total. ALLSELECTED respects what the user chose in the slicers and ignores only the visual's own filter — it is what you want in almost every percentage of the total, so the share adds up to 100% within the selection.
% do Total Geral =
DIVIDE( [Faturamento], CALCULATE( [Faturamento], REMOVEFILTERS( dProduto ) ) )
% do Selecionado =
DIVIDE( [Faturamento], CALCULATE( [Faturamento], ALLSELECTED( dProduto ) ) )
// The user picks 3 categories:
// ALL -> adds up to 41% | ALLSELECTED -> adds up to 100%A variable is evaluated once and reused — it avoids recalculating the same measure three times and makes the formula readable. An important detail: it holds the value in the context where it was declared, so it is not affected by a CALCULATE that comes afterwards.
Var % AA =
VAR Atual = [Faturamento]
VAR Anterior = CALCULATE( [Faturamento], SAMEPERIODLASTYEAR( dCalendario[Date] ) )
VAR Delta = Atual - Anterior
RETURN
IF( NOT ISBLANK( Anterior ), DIVIDE( Delta, Anterior ) )The measures that appear in practically every sales dashboard — and the details that make each one work.
TOTALYTD accumulates from the first day of the year to the last date in the context. If the company's fiscal year does not start in January, state the fiscal year end in the third argument — in retail it is common to close in June or July.
Faturamento YTD = TOTALYTD( [Faturamento], dCalendario[Date] )
// A fiscal year ending on 30 June
Faturamento YTD Fiscal = TOTALYTD( [Faturamento], dCalendario[Date], "06-30" )SAMEPERIODLASTYEAR shifts the context's whole period back by a year. For freer shifts (2 months, 3 quarters), use DATEADD. Both require a marked date table — without one, the result is silently incorrect.
Faturamento AA =
CALCULATE( [Faturamento], SAMEPERIODLASTYEAR( dCalendario[Date] ) )
Faturamento M-3 =
CALCULATE( [Faturamento], DATEADD( dCalendario[Date], -3, MONTH ) )A retail monthly series is too jagged to read a trend from (December always spikes). The moving average smooths it and shows the real direction. Use it as a line over the monthly bars, not in their place — the manager needs both.
Média Móvel 3M =
AVERAGEX(
DATESINPERIOD( dCalendario[Date], MAX( dCalendario[Date] ), -3, MONTH ),
[Faturamento]
)RANKX orders the values of a table according to an expression. The detail that trips up beginners: without ALL on the reference table, each row becomes the ranking's only element and the result is 1 for everyone.
Posição Loja =
RANKX( ALL( dLoja[Nome] ), [Faturamento], , DESC, DENSE )
// A ranking of the drop: who lost the most against the previous year
Posição Queda =
RANKX( ALL( dLoja[Nome] ), [Var % AA], , ASC, DENSE )TOPN returns a table with the top N rows, used inside CALCULATE to answer questions like how much do the 10 largest stores represent. A virtual table does not appear in the model: it exists only while the measure is being evaluated.
Fat Top 10 Lojas =
CALCULATE(
[Faturamento],
TOPN( 10, ALL( dLoja[Nome] ), [Faturamento], DESC )
)
Concentração Top 10 =
DIVIDE( [Fat Top 10 Lojas], CALCULATE( [Faturamento], ALL( dLoja ) ) )Instead of three nearly identical visuals, let the user choose the indicator in a slicer. Less screen, less maintenance, more control over what people read. The selection table is disconnected and the measure reads the choice with SELECTEDVALUE.
// Disconnected table: Métricas[Nome] = { "Faturamento", "Margem R$", "Unidades" }
Métrica Escolhida =
SWITCH(
SELECTEDVALUE( Métricas[Nome], "Faturamento" ),
"Faturamento", [Faturamento],
"Margem R$", [Margem R$],
"Unidades", [Unidades],
BLANK()
)A fixed title forces the reader to look at the slicers to know what they are seeing — and it is the most common cause of a dashboard screenshot being misread in the WhatsApp group. A text measure in the title solves it, and it survives the screen capture.
Título Página =
VAR Loja = SELECTEDVALUE( dLoja[Nome], "todas as lojas" )
VAR Periodo = SELECTEDVALUE( dCalendario[AnoMes], "o período selecionado" )
RETURN
"Faturamento de " & Loja & " em " & Periodo
// Visual > Title > fx > Format by field > Título PáginaConditional formatting by measure makes the rule explicit and reusable. A traffic light is useful when there is a clear target — but avoid painting everything: if the whole table is coloured, nothing draws attention. And never convey information by colour alone (see item 59).
Cor Atingimento =
VAR Ating = [Atingimento %]
RETURN
SWITCH( TRUE(),
Ating >= 1, "#1B7F4B", // hit the target
Ating >= 0.9, "#B8860B", // close
"#B23A48" // below
)
// Visual > Conditional formatting > Font colour > Format by fieldRatio measures (average ticket, margin %) show a value on the total row that sometimes makes no sense — an average of averages, individual targets added up. HASONEVALUE (or ISINSCOPE) lets you decide what to show in the total, instead of leaving a number that misleads.
Meta por Loja =
IF(
HASONEVALUE( dLoja[Nome] ),
[Meta],
BLANK() // in the total, the individual target makes no sense
)Design here is not decoration: it is the difference between the manager deciding in 10 seconds and sending an email asking for an explanation.
Show the screen to someone from the target audience for 5 seconds, hide it and ask: what did you see? is it good or bad? what would you do now? If they cannot answer, the problem is not a lack of data — it is a lack of hierarchy. Do this with 5 people before publishing: it is the cheapest feedback there is.
Script (10 minutes per person)
1. "Without clicking, look for 5 seconds." -> hide the screen
2. "What is this screen telling you?"
3. "Is the result good or bad?"
4. "What would you do with this information?"
5. "What was confusing?"
3 out of 5 people getting the same thing wrong = a design problem, not theirs.Western reading starts in the top-left corner. Put the most important indicator there, large. Context and detail go down and to the right. Filters sit in a fixed band (top or left-hand side) — never scattered, because the user needs to see at a glance what is filtered.
+---------------------------------------------------------------+
| Dynamic title [ Period v ] [ Region v ] | filters
+---------------------------------------------------------------+
| REVENUE MARGIN % AVG TICKET TARGET ATT. | what
| R$ 18.4 m 23.7% R$ 214 92% |
+---------------------------------------------------------------+
| Monthly trend (bars + PY line) | Top 10 stores (bars) | why
+--------------------------------------+------------------------+
| Table by store: revenue, var %, margin, attainment | where to act
+---------------------------------------------------------------+Each intent has a shape the brain reads faster: comparing across categories calls for bars; evolving over time calls for a line; composing a whole calls for a stacked bar; relating two measures calls for a scatter plot. A pie chart only with 2 or 3 slices — the eye compares angles far worse than lengths.
Compare categories -> horizontal bars, sorted by value
Evolve over time -> line (continuous) or columns (discrete periods)
Compose a whole -> 100% stacked bar > pie
Relate 2 measures -> scatter (e.g. margin x volume per store)
Distribute -> histogram
1 number + a target -> card with the variation, not a gaugeThree palettes, one use for each: categorical (distinct colours for items with no order), sequential (intensity for magnitude) and diverging (two poles with a neutral in the middle, for variation around zero). Fix each channel's colour across the whole report: if e-commerce is blue, it is blue on every page. And keep most of the screen grey — colour is for what needs attention.
Categorical channels: store #2B6CB0 | e-commerce #2C7A7B | telesales #6B46C1
Sequential volume: #EBF4FF -> #1A365D
Diverging var %: #B23A48 <- #E8E8E8 -> #1B7F4B
The 60-30-10 rule: 60% neutral, 30% support, 10% highlight.Around 8% of men have some colour vision deficiency — on a board of 12, someone probably cannot tell your red from your green. Never convey information by colour alone: add an arrow, a sign or a label. Guarantee a minimum contrast of 4.5:1 on text, fill in the visuals' alt text and review the tab order.
Bad: [ ] green [ ] red
Good: v +8.2% ^ -3.1% (symbol + sign + colour)
Checklist before publishing:
[ ] contrast >= 4.5:1 (text) and 3:1 (graphical elements)
[ ] nothing conveyed by colour ALONE
[ ] alt text on every visual (Format > General > Alt text)
[ ] tab order reviewed (View > Tab order)
[ ] font >= 10 pt; labels without a 45-degree rotationR$ 18,437,219.63 on a card is noise: nobody decides with the cents. Abbreviate in the executive view (R$ 18.4 m) and leave the detail for the working table. Numbers always right-aligned, text left-aligned, and the same number of decimal places throughout the column.
Executive card: R$ 18.4 m (1 decimal, abbreviated unit)
Analytical table: 18,437,219.63 (2 decimals, right-aligned)
Percentage: 23.7% (1 decimal; 2 only if the decision demands it)
Variation: +8.2 p.p. (percentage point != percentage)Every drop of ink on the screen should carry information. A double border, a shadow, a coloured background, a gradient, a 3D chart and a redundant axis all compete with the data. A clean dashboard is not an empty dashboard: it is one where everything left has a function.
Remove: 3D, shadow, gradient, decorative border,
heavy gridlines, the Y axis when there are data labels,
the legend when there is a single series,
an icon that merely repeats the text beside it
Keep: data labels OR the axis (never both),
white space between blocks (at least 8 px)The user needs to know what will happen before clicking. Standardise it: a slicer filters the whole page; clicking a bar highlights the others; the detail lives in an explicit drill-through. And always offer the way back — a visible Clear filters button solves half the support tickets.
Edit interactions (Format tab): define it visual by visual
KPI -> not affected by a click on the stores chart
Map -> highlights, does not filter
Buttons you cannot do without:
[ Clear filters ] a bookmark with "Data" on and no selection
[ Back ] on the drill-through pages
A page tooltip for rich detail (a mini chart on hover)A beautiful dashboard with beautiful data is easy. What destroys trust is the blank screen when the filter returns nothing, or the zeroed value when the 6 a.m. refresh failed. Write explicit messages and always show the last refresh date.
No data under the filter:
"No sales for Region = South in March/2026.
Try widening the period." (do not leave the screen blank)
A fixed footer, on every page:
"Data up to 19/08/2026 06:12 · source: DW_Vendas · questions: bi@empresa.com"
A freshness measure:
Última Atualização = MAX( fVendas[DataCarga] )Sketching before building: 40 minutes in Figma save two weeks of rework in Power BI.
Changing a rectangle in Figma costs seconds; changing a finished dashboard costs remodelling, new measures and fresh validation. The sketch also changes the conversation with the client: they stop debating whether the chart looks good on the right and start debating whether the question is the right one.
The cost of changing a layout decision
Paper sketch ~ 2 min
Figma wireframe ~ 10 min
Figma prototype ~ 30 min
Built dashboard ~ 2 to 5 days (model + measures + testing)No colour, no real data, no nice font: grey boxes with labels. The goal is to discuss what goes in each area and in what order of importance. The low fidelity is an advantage — nobody argues about a shade of blue in a grey drawing, and that is exactly what you want at this stage.
Frame 1280 x 720 (the proportion of the Power BI canvas, 16:9)
[ band ] title + filters height 72
[ 4 boxes ] KPIs height 120
[ 2 boxes ] trend | ranking height 260
[ 1 box ] detail table height 200
Write in each box the QUESTION it answers.Define colour, typography and spacing once in Figma (as styles or variables) and export them to a JSON theme. The dashboard is born consistent, and a rebrand stops requiring 40 visuals to be repainted by hand.
{
"name": "Casa & Construção 2026",
"dataColors": ["#2B6CB0", "#2C7A7B", "#6B46C1", "#B7791F", "#B23A48"],
"background": "#FFFFFF",
"foreground": "#1A202C",
"tableAccent": "#2B6CB0",
"textClasses": {
"title": { "fontFace": "Segoe UI Semibold", "fontSize": 14, "color": "#1A202C" },
"label": { "fontFace": "Segoe UI", "fontSize": 10, "color": "#4A5568" }
}
}Create the KPI card once, as a component, with variants for each state (above target, below, no data). Every card in the file inherits the change. In Power BI the equivalent is grouping the visuals and reusing the group across pages, keeping the theme.
Component: KPI Card
Properties: label, value, variation, state
Variants: state = positive | negative | neutral | empty
Rule: variation always with an arrow + sign + colour
Fixed size: 296 x 120 (8 px grid)Link the frames simulating a drill-through, a back button and a page change. In 15 minutes you discover the board wanted to start from the regional view, not the national one — a discovery that, made later, would cost you the whole filtering logic.
The minimum flow to prototype
Home (national)
-> click a region -> Regional page
-> click a store -> Store drill-through
-> [ Back ] -> Regional page
-> [ Clear filters ] -> HomeWork at 1280x720 (the proportion of the Power BI canvas) on an 8 px grid, and the layout becomes a position in the dashboard almost directly — Power BI accepts a numeric X, Y, width and height per visual. Agree on the names too: the wireframe's label should be the measure's name.
Figma Power BI
frame 1280 x 720 -> View > Page size > 16:9
8 px grid -> Format > General > position (multiples of 8)
colour style -> imported JSON theme
KPI component -> group of visuals copied between pages
label "Margem %" -> the measure name [Margem %]Delivering is not the end. Without measuring use, you do not know whether you built a tool or an ornament.
Every dashboard gets visits at launch — curiosity brings them. What matters is the return: how many came back the following week, how often, and whether the detail pages get used. A dashboard with 200 opens on day one and 6 in month two is dead, however many compliments it got.
Adoption unique users / target audience target: > 60% in 30 days
Retention came back the following week target: > 40%
Frequency opens per user per week
Depth pages per session is the detail used?
Time to insight seconds until the first filter > 30 s = confusingThe Service records views per report, per page and per user, and it lets you save that usage report as its own semantic model, to follow the historical series. It is the most direct source for finding out which pages nobody opens — natural candidates to disappear.
Workspace > report > ... > View usage metrics report
Questions it answers:
which pages get opened (and which never do)
who opened it in the last 30 days
origin: browser, mobile app, embedded
A page with 0 visits in 60 days: remove it, or find out why nobody finds it.Five users reveal most usability problems. Give them tasks, not questions: find out which store fell the most in March. Stay quiet and time it. You will watch someone look for the filter in three places before finding it — and that is worth more than any opinion collected in a meeting.
Task script (record the screen, with permission)
T1. What was March's revenue? expected < 10 s
T2. Which store fell the most against last year? expected < 30 s
T3. In that store, which category drove the drop? expected < 45 s
T4. Export the list of stores below target. expected < 30 s
Note: time, wrong clicks, hesitation, anything said out loud.Did you like it? only produces politeness. Ask about past, concrete behaviour: show me what you did with last week's number. And be wary of feature requests — when someone asks to export to Excel, the real problem is nearly always that the screen does not answer their question.
Avoid Prefer
"Did you like the dashboard?" "Show me how you used it yesterday."
"Do you want a pie chart?" "What decision do you need to make here?"
"Is it clear?" "Without clicking: what does this screen say?"
"Is anything missing?" "What forced you to open Excel afterwards?"Above 3 seconds per interaction, the user loses their train of thought. Use the Performance Analyzer to find the slow visual and DAX Studio to investigate the query. The usual culprits: a visual with 40 thousand rows, a measure with FILTER over the whole fact, and a card doing a DISTINCTCOUNT on a high-cardinality column.
View > Performance analyzer > Start recording > Refresh visuals
How to read the numbers:
DAX query > 1000 ms -> a measure or model problem
Display > 500 ms -> a visual with too many points/lines
Other high -> too many visuals on the page (keep it < 15)
A practical target: the page opens in < 3 s, an interaction responds in < 1 s.Before you publicise it, sort out who sees what with RLS: the Recife store manager sees only their own store, and the same screen serves 42 managers. Then treat the dashboard as a living product — hypothesis, change, measurement — and remove without mercy whatever the usage data shows nobody opens.
// Modeling > Manage roles > "Gerente Loja"
// DAX filter on the dLoja table:
[Email] = USERPRINCIPALNAME()
// A monthly cycle
1. Hypothesis: "nobody finds the channel filter (it is on the right)"
2. Change: move it to the top band, with the others
3. Measure: does use of the filter go up? does time to first filter go down?
4. Decision: keep, revert or test something else