An Elegant Solution to A Common Power BI Challenge
Anyone who has built a KPI summary in Power BI has hit the same wall. The matrix visual formats a whole column the same way, so revenue, units and margin cannot each carry their own format. You either compromise on presentation or hand-build a table that breaks the moment someone asks for a new KPI or a YTD column. This technical insight sets out a DAX pattern that solves it properly, using disconnected tables, SWITCH and FORMAT so one measure drives the entire matrix.
What's In the Whitepaper?
Lee Channon walks through the full build, from model setup to the finished visual, with working code and a downloadable Power BI file. It covers:
- The two disconnected tables that drive the rows and columns of the matrix
- A SWITCH(TRUE()) display measure that returns the right value, formatted correctly, for every cell
- A companion measure that colours actuals green or red against target
- Practical tips on grouped column headers, organising measures at scale, and why your date table matters in production
- How the pattern holds up as the matrix grows: adding a KPI is a row, a measure and a few lines of DAX
Written for report developers and analysts who are past the fundamentals and building reports other people rely on.
Lee Channon
Principal Consultant at Montage