Matrix: group by measures (link between table with categorised measure names and actual measures)

[BeautifulDash]

I'd like a matrix with all the calculated measures for rows, subdivided, and the quarters of the year as columns.

I have a separate table with a hierarchy between Category and Subcategory for the measures, and the rows are displaced correctly. 
I also wrote the measures, and they calculate correctly. 
Grouping per quarter works, as I have a dedicated Date table that links with the Date column in the Incidents table, usual for time intelligence functions.
How can I however link the Subcategories to the measures with the exact same name to group?

I thought to add a calculated table but one formula for the different calculations per each row is not feasible (number of, ratio, ..).

	Q1	Q2	Q3	Q4
Category: Incidents
Subcategory: Number of Incidents	Number of Incidents as a measure
Subcategory: Number of Investigations
Subcategory: Ratio of Incidents to Investigations

[BeautifulDash]

Thank you, that worked well.

How can I build a hierarchy for my measures used, so I can drill down in the matrix?

For instance:
+ Calculations for Product Categories

-- Calculations for Cars

-- Calculations for Laptops

+ Calculations for Non-Product Categories

-- Calculations for Employees

-- Calculations for Stores

[v-kkf-msft]

Hi @BeautifulDash ,

You need to correspond the categories to the subcategories, like this:

Then use different measures for different levels.

Measure = 
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Table'[SubCategory] ), [Measure2],
    ISINSCOPE ( 'Table'[Category] ), [Measure1],
    [Measure3]
)

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BeautifulDash]

Thank you very much for replying.

I think this approach only works when one measure (for instance, a simple division) is applied across all drilled-down categories, whereas I am trying to use one specific measure per drilled-down subcategory.

To illustrate; how I attempted to solve it, but I cannot seem to add this to a matrix to display correctly:

[v-kkf-msft]

Hi @BeautifulDash ,

Please try the following formula:

MatrixMeasure = 
CALCULATE (
    SWITCH (
        TRUE (),
        ISFILTERED ( 'Table'[SubCategory] ),
            SWITCH (
                MAX ( 'Table'[SubCategory] ),
                "SUBCAT A", SWITCH ( MAX ( 'Table'[Index] ), 1, [Pro_SubCat_A], 6, [NonPro_Category] ),
                "SUBCAT B",
                    SWITCH (
                        MAX ( 'Table'[Index] ),
                        2, [Pro_SubCat_B_1],
                        3, [Pro_SubCat_B_2],
                        4, [Pro_SubCat_B_3],
                        5, [Pro_SubCat_B_4],
                        7, [NonPro_SubCat_B_1],
                        8, [NonPro_SubCat_B_2],
                        9, [NonPro_SubCat_B_3]
                    )
            ),
        ISINSCOPE ( 'Table'[Category] ),
            SWITCH (
                MAX ( 'Table'[Category] ),
                "Product", [Pro_Category],
                [NonPro_Category]
            )
    ),
    TREATAS ( VALUES ( 'Table'[Category] ), 'Fact'[Category] ),
    TREATAS ( VALUES ( 'Table'[SubCategory] ), 'Fact'[SubCategory] )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BeautifulDash]

Many thanks for your excellent feedback.

What is the Fact table you are referring to? I do not understand the second parameter of the TREATAS DAX functions. 

And how do you build the above matrix using the measure? Quarters is columns but is MatrixMeasure rows or values?

[v-kkf-msft]

Hi @BeautifulDash ,

I am not sure if I understood your question correctly. If you want to display the measures as rows as in the figure below, then you can enable the "Switch values to rows" option in the Values pane.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.