Great point Adam, I hadn't noticed that the rows were not categories, but a second set of measures. There are certainly several options to support two dynamic dimensions, and the use of Tabular Editor and Calculation Groups is probably the most elegant but does require the use of that third-party tool (definitely widely used and trusted).
If you are trying to keep thing a little more
simple (and I use that term loosely), one other option would be to create a separate table, commonly referred to as a disconnected slicer, to define the intersection of the separate concerns, the business measures (i.e. Sales, Revenue, ...) and time-intelligence measures (Current Year, Prior Year, ...). Power BI is starting to create wizards to support capabilities such as this, but it helps to understand what it is effectively doing behind the scenes.
What I have done here is a two step process, the first defines a table with the combinations of the two concerns you are looking to present, and the second associates a specific measure to use for each intersection.
Step 1:Use the "New Table" feature and shown DAX to create a separate configuration table, where each Key represents a unique intersection of the two dimensions. This could also be created in Power Query but this achieves the same result.
Step 2:Add a DAX measure (called "Selected Measure" here) that depending on which combinations of the two separate dimension is selected, dictates which other measure or calculations to display (and how to format it).
I have included the DAX below for reference:
Measurement Configuration = DATATABLE(
"Measurement Key", INTEGER,
"Measurement Name", STRING,
"Measurement Name Order", INTEGER,
"Measurement Type", STRING,
"Measurement Type Order", INTEGER,
{
{1, "Sales", 1, "Current Year", 1},
{2, "Sales", 1, "Prior Year", 2},
{3, "Sales", 1, "Change", 3},
{4, "Revenue", 2, "Current Year", 1},
{5, "Revenue", 2, "Prior Year", 2},
{6, "Revenue", 2, "Change", 3},
{7, "Shipped", 3, "Current Year", 1},
{8, "Shipped", 3, "Prior Year", 2},
{9, "Shipped", 3, "Change", 3}
}
)
Selected Measure = SWITCH(SELECTEDVALUE('Measurement Configuration'[Measurement Key]),
1, FORMAT([$ Current Year Sales], "$ #,##0"),
2, FORMAT([$ Prior Year Sales], "$ #,##0"),
3, FORMAT([$ Current Year Sales] - [$ Prior Year Sales], "$ #,##0"),
4, FORMAT([$ Current Year Revenue], "$ #,##0"),
5, FORMAT([$ Prior Year Revenue], "$ #,##0"),
6, FORMAT([$ Current Year Revenue] - [$ Prior Year Revenue], "$ #,##0"),
7, FORMAT([# Current Year Units], "#"),
8, FORMAT([# Prior Year Units], "#"),
9, FORMAT([# Current Year Units] - [# Prior Year Units], "#"),
BLANK())
------------------------------
Jared Brown
Vice President, Data and Analytics
Tallan
------------------------------
Original Message:
Sent: Jan 11, 2023 11:41 AM
From: Adam Artur Boltryk
Subject: Static Row Headers with calculated row values
Hi Mike,
Hard to say if we can't see INPUT, only desired output.
1. for me 'Returns', 'Sales' and 'Shipped' should be MEASURES.
2. all columns with time intelligence (including these not presented like - Current month, Current week, Last Monday, Previous quarter and all you can name) you can create as separate measures or use eg. Tabular editor.
Regards,
------------------------------
Adam Artur Boltryk
Business Analyst
Original Message:
Sent: Jan 10, 2023 09:46 AM
From: Mike McLean
Subject: Static Row Headers with calculated row values
Hello, newer user ro power bi desktop coming from Qlik Sense. In Qlik I was able to create a table with what I call static row headers and a row of calcualted fields. Here is what I am trying to accomplish, this is just a made up sample visual table.
Current Year Prior Year Year Over Year Change
Returns 50 40 10
Sales 125 105 20
Shipped 45 30 15
The first column Returns, Sales, and Shipped are static values that just describe the row of data. The other 3 columns are calculated values. Any help would greatly be appreciated.
------------------------------
Mike McLean
------------------------------