Forum Discussion

VanshikaB's avatar
VanshikaB
Frequent Visitor
1 year ago
Solved

Creating Hierarchy for measures

Hi, How to create hierarchy for measures in matrix table?   As shown in the pic below, Net Sales is at top (has level 1 hierarchy) and 'Net Trade Sales' and 'Trade Sales' have a level 2 hierarchy  ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi VanshikaB ,

     

    I think you can try ISINSCOPE() function to update your measure.

    In my sample:

    Display Sales =
VAR _1 =
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( 'Hierarchy'[Level 2] ) = "Net Trade Sales", [Net Trade Sales],
        SELECTEDVALUE ( 'Hierarchy'[Level 2] ) = "Trade Sales", [Trade Sales],
        SELECTEDVALUE ( 'Hierarchy'[Level 1] ) = "Net Sales", [Net Sales],
        SELECTEDVALUE ( 'Hierarchy'[Level 1] ) = "Interco Sales", [Interco Sales],
        BLANK ()
    )
RETURN
    IF (
        ISINSCOPE ( 'Hierarchy'[Level 2] ),
        IF ( MAX ( 'Hierarchy'[Level 2] ) = BLANK (), BLANK (), _1 ),
        IF ( ISINSCOPE ( 'Hierarchy'[Level 1] ), _1 )
    )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

     

123abc's avatar
123abc
Community Champion
1 year ago

Steps:

  1. Create Individual Measures: Create separate measures for each of the calculations (Net Sales, Net Trade Sales, Trade Sales, etc.).

    DAX
     
    Net Sales = SUM(Sales[Net Sales]) Net Trade Sales = SUM(Sales[Net Trade Sales]) Trade Sales = SUM(Sales[Trade Sales]) Interco Sales = SUM(Sales[Interco Sales])

  2. Create a Switch Measure: Use a switch measure to control what should be displayed based on the selected hierarchy level.

    DAX
    Display Sales = SWITCH( TRUE(), SELECTEDVALUE(Hierarchy[Level]) = "Net Sales", [Net Sales], SELECTEDVALUE(Hierarchy[Level]) = "Net Trade Sales", [Net Trade Sales], SELECTEDVALUE(Hierarchy[Level]) = "Trade Sales", [Trade Sales], SELECTEDVALUE(Hierarchy[Level]) = "Interco Sales", [Interco Sales], BLANK() )

  3. Create a Hierarchy Table: You can manually create a small table that defines the hierarchy for your measures.

    Example table for hierarchy:

    Level
    Net Sales
    Net Trade Sales
    Trade Sales
    Interco Sales

  4. Add the Hierarchy to the Matrix:

    • Drag the Level field (created from the hierarchy table) to the rows section of the matrix.
    • Place the Display Sales measure in the values section.
    • This will simulate the hierarchy and display the child measures (e.g., Net Trade Sales and Trade Sales) under the parent measure (Net Sales).

This approach will create a visual structure that mimics hierarchical measures in Power BI. Let me know if you need more clarification or assistance!