Every KPI in the atlas comes with the code to calculate it in three places: a DAX measure for Power BI, a SQL query for a database or warehouse, and an Excel formula. This lesson takes one into each.
Open the code
Open a KPI and scroll to Ready to copy. The tabs on the right switch between DAX, SQL and Excel, and the atlas remembers the one you used last.
Click Copy in the corner of the code.
Put the DAX into Power BI
In Power BI Desktop:
- Select the table the measure belongs to in the Data pane
- Choose New measure on the ribbon
- Paste, and press Enter
The measure's name is the line before the =. Keep it: it is clear, and it matches the KPI's name in the atlas and in your spec.
Rename to your model
The code uses example names, said in plain words: Opportunities[Stage], Sales[Quantity], a 'Date' table. Your model has its own. Replace each with yours, and if the code uses a measure that does not exist yet, like [Revenue], create that one first (it is in the atlas too).
Use the SQL
Switch to SQL. The queries are written in standard SQL with PostgreSQL-style dates (DATE_TRUNC, INTERVAL), and work with small changes in Snowflake, BigQuery, Redshift and DuckDB. SQL Server uses DATEADD and DATEFROMPARTS instead.
Most queries return one row per period or per group, ready to chart or to load into a report.
Use the Excel formula
Switch to Excel. The formulas use table references like Opps[Stage], so turn your data into a table first (Ctrl + T) and name it. Some formulas use named cells, like Revenue or Target, for numbers you have already calculated.
Check the result
Before you publish, compare your number with the Example in the facts and the Benchmark. If your win rate is 90%, check the denominator before you celebrate: the common mistakes list probably names what went wrong.
The next lesson takes many KPIs at once.