Skip to content
Controllingblog

Home❧Software & Data❧Power BI for controllers: getting started, DAX and your first dashboard

Power BI for controllers: getting started, DAX and your first dashboard

From Excel report to interactive dashboard: what DAX is, which 15 functions you really need and how to build your first controlling dashboard in a few steps.

Software & Data15 min readUpdated 14 Nov 2026
Illustration: laptop with dashboard, database and cloud +12% 4.2 M 98%

Power BI is Microsoft's tool for data analysis and visualisation. It is particularly interesting for controllers because much of it feels familiar: you know Power Query from Excel, and the DAX formula language resembles Excel functions. At the same time the way of thinking is different – and that is exactly where many beginners fail. This article takes you from the basics to your first finished dashboard.

What Power BI does better than Excel

  • Large data volumes: millions of posting lines are no problem.
  • Interactivity: one click on a cost centre filters the whole report.
  • Automatic refresh: data is reloaded on a schedule, nobody has to copy anything.
  • Distribution: reports are shared in the browser or via app, with access rights per user.

Excel remains better for planning, ad-hoc calculations and anything you need to work out freely. The two tools complement each other.

What is DAX?

DAX stands for Data Analysis Expressions, the formula language of Power BI, Power Pivot in Excel and Analysis Services. At first glance it looks like Excel: SUM, IF, DIVIDE. The big difference: Excel calculates with cells, DAX calculates with tables and columns – always in the context of what is currently selected in the report.

If you write =SUM(C2:C500) in Excel, the result is a fixed number. If you write Revenue = SUM(Postings[Amount]) in DAX, you create a measure that adapts automatically: for January it shows January revenue, for the Sales cost centre Sales revenue, for both together January revenue in Sales. This dependence on the selected slice is called the filter context – the most important concept in DAX.

Rule of thumbIn Excel you build a separate formula for every view. In DAX you write a measure once – and it works in every table, every chart and every filter.

Why DAX is ideal for interim solutions

Intermediate steps inside the formula: with VAR and RETURN you store intermediate results in variables. Long calculations become readable, faster and easier to check – much like helper columns in Excel, only without extra columns.

CM ratio = VAR revenue = [Revenue] VAR cm = [Revenue] - [Variable costs] RETURN DIVIDE(cm, revenue)

An interim solution for the company: many mid-sized businesses don't (yet) have a data warehouse or planning software. Power BI with Power Query and DAX closes this gap remarkably well: data from accounting, ERP and Excel plans is combined, cleaned and calculated – without a big IT project. The logic you build along the way (account mapping, KPI definitions, hierarchies) is later the perfect blueprint for a real data warehouse. None of it is lost.

  • quick to get going – first results often within days,
  • Power BI Desktop is free,
  • grows with you: from an Excel file to a database to a data warehouse as the source,
  • the business logic stays with controlling.

Step 1: Load data with Power Query

Typical sources are a posting journal from financial accounting, master data (accounts, cost centres) and plan figures from Excel. Power Query cleans them: rename columns, set data types, remove empty rows. Every step is saved and repeated automatically on the next refresh.

Step 2: The data model – think in stars

The most important difference from Excel: instead of one big table you build a star schema. In the middle is the fact table with the postings, around it the dimension tables: calendar, chart of accounts with P&L structure, cost centres, possibly plan.

Postings (facts)DateAccount · Cost centreΣ Amount CalendarDate · YearMonth · Quarter AccountsAccount · NameP&L group Cost centresCC · DivisionOwner PlanMonth · AccountPlan value 1*1*
Recreated model view: the fact table in the middle, dimensions around it. “1” and “*” show the one-to-many relationships.

The tables are linked in model view via keys (one-to-many relationship). A dedicated calendar table is indispensable – it is the prerequisite for all time comparisons such as prior year or year-to-date.

The 15 most important DAX functions

Depending on regional settings, Power BI separates arguments with commas or semicolons – commas are used here.

Basic calculations

1. SUM

Sums a column. The basis of almost every measure.

Revenue = SUM(Postings[Amount])

2. COUNTROWS

Counts the rows of a table, e.g. the number of postings or invoices.

Number of invoices = COUNTROWS(Invoices)

3. DISTINCTCOUNT

Counts distinct values – such as the number of active customers in a month.

Active customers = DISTINCTCOUNT(Postings[CustomerNo])

4. DIVIDE

Divides safely: division by zero returns a blank instead of an error. Always use it instead of “/”.

EBIT margin = DIVIDE([EBIT], [Revenue])

Controlling filters – the heart of DAX

5. CALCULATE

Evaluates an expression under modified filters. Understand CALCULATE and you understand half the language.

Revenue Sales = CALCULATE([Revenue], CostCentres[Division] = "Sales")

6. FILTER

Returns a filtered table – for conditions CALCULATE alone can't express.

Large orders = CALCULATE([Revenue], FILTER(Orders, Orders[Value] > 50000))

7. ALL

Removes filters. Essential for shares of the total.

Share of total = DIVIDE([Revenue], CALCULATE([Revenue], ALL(CostCentres)))

8. SELECTEDVALUE

Returns the selected value of a filter – handy for dynamic titles or scenario switches.

Title = "Report for " & SELECTEDVALUE(Entity[Name], "all entities")

Row-by-row calculation and relationships

9. SUMX

Calculates row by row and then sums – for example quantity × price when there is no ready-made amount column.

Revenue = SUMX(Sales, Sales[Quantity] * Sales[Price])

10. RELATED

Fetches a value from a related table – the DAX counterpart to XLOOKUP.

PL group = RELATED(Accounts[PL group])

11. SWITCH

Several conditions, clearly laid out – ideal for traffic lights or KPI selection via a switch.

Traffic light = SWITCH(TRUE(), [Var %] >= 0, "green", [Var %] >= -0.05, "amber", "red")

12. RANKX

Creates rankings, such as the top 10 customers by contribution margin.

Rank = RANKX(ALL(Customers[Name]), [Contribution margin])

Time intelligence

13. TOTALYTD

Accumulates from the start of the year to the selected date – the classic year-to-date value.

Revenue YTD = TOTALYTD([Revenue], Calendar[Date])

14. SAMEPERIODLASTYEAR

Shifts the period back by one year – for the prior-year comparison.

Revenue PY = CALCULATE([Revenue], SAMEPERIODLASTYEAR(Calendar[Date]))

15. DATEADD

Shifts by any interval, e.g. one month back for the prior-month comparison.

Revenue PM = CALCULATE([Revenue], DATEADD(Calendar[Date], -1, MONTH))
Practical tipAlways create measures explicitly instead of just dragging columns into a visual, and collect them in a separate empty table called “_Measures”. That way you keep control of the calculations and find them again quickly.

Step by step: your first controlling dashboard

  1. Install Power BI Desktop – free via the Microsoft Store (link below).
  2. Get data: Home → Get data → Excel or Text/CSV. Load the posting journal, chart of accounts, cost centres and plan values.
  3. Clean: in the Power Query editor rename columns, set data types (date, decimal number), remove empty rows. “Close & Apply”.
  4. Create a calendar table: Modeling → New table: Calendar = CALENDARAUTO(), then add year and month columns and mark it as a date table.
  5. Draw relationships: in model view connect Date → Calendar[Date], Account → Accounts, Cost centre → Cost centres.
  6. Write measures: revenue, contribution margin, EBIT, plan, variance absolute and in percent, prior year, year-to-date – using the functions above.
  7. KPI cards: “Card” visual for revenue, contribution margin, EBIT and liquidity, each with the variance to plan below.
  8. Monthly trend: column chart with actual as column and plan as line or outline.
  9. Variances by division: bar chart, positive values green, negative red.
  10. Filters: slicers for entity, division and period at the side.
  11. Publish: share in the Power BI service and set up a scheduled refresh (on-premises sources need a gateway; sharing usually requires a paid licence).
Monthly report · October Entity ▾Year 2026 ▾ Revenue 4.21m+3.2% vs planContribution margin 1.63m−1.1% vs planEBIT 412k+0.8% vs planLiquidity 1.08mUnused line: 600k Revenue by month: actual vs plan Variance by division SalesProductionPurchasingAdminIT
Recreated example report: KPI cards at the top, actual (filled) vs plan (outlined) on the left, variances by division on the right – following IBCS design rules.

A realistic learning path

  1. Weeks 1–2: Power Query and data model with your own posting data.
  2. Weeks 3–4: basic DAX measures and time intelligence.
  3. Weeks 5–6: first dashboard for management, gather feedback.
  4. After that: integrate plan figures, variance analyses, drill-down to the posting.

Typical beginner mistakes

  • One huge flat table instead of a star schema.
  • No calendar table – time comparisons then don't work reliably.
  • “/” instead of DIVIDE – and suddenly errors in reports.
  • Too many visuals on one page.
  • Calculations duplicated and contradictory in Power Query and DAX.

Conclusion

Power BI is worth it for controllers who regularly build the same analyses and make them available to several people. With a star schema, a handful of DAX functions and a clear report structure, you create a tool within a few weeks that also works as an interim solution until a data warehouse is in place – and whose logic lives on afterwards.

Further links

Frequently asked questions

What is DAX?

DAX (Data Analysis Expressions) is the formula language of Power BI, Power Pivot and Analysis Services. It calculates with tables and columns and automatically adapts results to the selection in the report.

What is the difference between a measure and a calculated column?

A calculated column is stored for each row at load time. A measure is only calculated when displayed, in the current filter context, and is almost always the better choice for KPIs.

Can Power BI replace a data warehouse?

For small and medium data volumes it can be a very good interim solution. With many sources, large volumes or high demands on history and governance, a data warehouse as the source for Power BI is the better foundation.

Is Power BI free?

Power BI Desktop for creating reports is free. Sharing and publishing usually require paid licences.