Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Yes, you can yes DAX measures as categories. While this may not be an out of the box feature, this is possible.
I find this especially useful when switching from one measure to another using a slicer and in visualizing the measures in a column chart wherein I could assign them to a legend.
For this post I used the VAN Arsdel sample workbook I modified a bit.
I created the following measures:
Total Gross Sales = SUM ( financials[Gross Sales] )
Total Discounts = SUM ( financials[Discounts] )
Total Net Sales = SUM ( financials[ Sales] )
Total Cost of Goods Sold = SUM ( financials[COGS] )
Total Profit = SUM ( financials[Profit] )
and then a disconnected (no relationship with Fact) table I call Financials Summary Table.
Financials Summary Table = DATATABLE ( "Category", STRING, "Type", STRING, "Index", INTEGER, { { "Gross Sales", "Non-Deduction", 1 }, { "Discounts", "Deduction", 2 }, { "Net Sales", "Non-Deduction", 3 }, { "Cost of Goods Sold", "Deduction", 4 }, { "Profit", "Non-Deduction", 5 } } )
If you are not comfortable with using this formula, you may use Enter Data though it would be good to get exposed to it. The DAX formula above creates a calculated table containing three columns: Category, Type and Index. I added an index column for two reaons:
To call the five measures, I used this formula:
Financials = VAR SelectedMeasure_ = SELECTEDVALUE ( 'Financials Summary Table'[Index] ) //you may use any function that returns just one value RETURN IF ( HASONEVALUE ( 'Financials Summary Table'[Index] ), SWITCH ( SelectedMeasure_, 1, [Total Gross Sales], 2, [Total Discounts], 3, [Total Net Sales], 4, [Total Cost of Goods Sold], [Total Profit] ) )
If I did not add an index column, I would have entered each row value in my calculated table Category column in the formula instead of the numbers 1 to 4.
I created another measure to show percentage of a measure over total gross sales:
Finacials % = DIVIDE ( [Financials], [Total Gross Sales] )
Since the Financials measure references the other measures from financials table, it will still get crossfiltered by the columns in the latter
Here's my sample PBIX: https://drive.google.com/open?id=1UNafWJRndZJuDhzRpNH1tKZVIdBeWov9
Proud to be a Super User!
Solved! Go to Solution.
Proud to be a Super User!
Hi Guys,
Do you know if in this scenario is it possible to sort by measure value in matrix table?
Thank you.
Regards,
Pawel
How to call a categorical column in that switch syntax? For example, If I need to bring in Age for index 1 and Gender for Index 2 and so on..., any ideas?
Hi danextian ,
Slicer is based on the column while measures are also based on columns so that you can use slicer to select the measure you want to use. In addtion, you said "I find this especially useful when switching from one measure to another using a slicer and in visualizing the measures in a column chart "
<-- What does it mean and what's your expected result?
Regards,
Jimmy Tao
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |