Forum Discussion

BeautifulDash's avatar
BeautifulDash
Icon for Helper I rankHelper I
4 years ago

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

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, ..).

 

 Q1Q2Q3Q4
Category: Incidents    
Subcategory: Number of IncidentsNumber of Incidents as a measure   
Subcategory: Number of Investigations    
Subcategory: Ratio of Incidents to Investigations    
Need Help

6 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.

  • BeautifulDash's avatar
    BeautifulDash
    Icon for Helper I rankHelper I
    4 years ago

    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's avatar
      v-kkf-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      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's avatar
        BeautifulDash
        Icon for Helper I rankHelper I
        4 years ago

        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: