Use DAX Measures as Categories

[danextian]

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 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

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

[Wiatrak1]

Hi Guys,

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

Thank you. 

Regards,

Pawel 

[Anonymous]

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]

Hey @danextian , thanks, you rock!!!

[v-yuta-msft]

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

[danextian · Accepted solution]

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