Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
danextian
Super User
Super User

Use DAX Measures as Categories

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.

measures as categories.png

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 use it as a column to sort the Category column by
  • to simplify the creation of a measure that will call the five previously created measures.

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

 

measures as categories2.png

 

 

Here's my sample PBIX: https://drive.google.com/open?id=1UNafWJRndZJuDhzRpNH1tKZVIdBeWov9






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
1 ACCEPTED SOLUTION

Hello, the label for my post is Tips and Tricks, not need help.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

View solution in original post

5 REPLIES 5
Wiatrak1
New Member

Hi Guys,


Do you know if in this scenario is it possible to sort by measure value in matrix table?

 

Thank you. 

Regards,

Pawel 

abhinavspace
New Member

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?

Oscar_Mtz_V
Kudo Commander
Kudo Commander

Hey @danextian , thanks, you rock!!!

v-yuta-msft
Community Support
Community Support

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

 

 

Hello, the label for my post is Tips and Tricks, not need help.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.