Creating Hierarchy for measures

[VanshikaB]

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 

[powerbiexpert22]

Hi @VanshikaB ,

Please try below option

[VanshikaB]

Hi @123abc,
Not getting the results as you mentioned
Below are the steps that I did, pls let me know if there is some mistake in the steps,

1) Created measures
2) Created Hierarcy table - 

3) Created Switch Measure
4) Created Matrix

Also can drill down option be added to level1 Hierarchy?

[Anonymous]

Hi @VanshikaB,

I think 123abc's reply is helpful, however the code needs to be updated. If we add Level 1 = Net Sales firstly, all level 2 below it will return [Net Sales].

Display Sales = 
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()
)

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.

[VanshikaB]

Hey,
Got the required hierarchy format.
Although there is an issue - Interco Sales does not have a Level 3 hierarchy so it is giving blank after drill down, is there a way to remove the blanks?

This blank issue can appear whenever there are no further hierarchies

[Anonymous · Accepted solution]

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.

[VanshikaB]

Hi,
Thanks its working

[123abc]

Adjusting Steps for Proper Measure Hierarchy

1. Hierarchy Table: Based on the file and images, you created a Hierarchy table with levels like Net Sales, Net Trade Sales, Trade Sales, etc. This is correct, but we need to ensure that this hierarchy is linked properly with the measures and matrix structure.

2. Switch Measure: Make sure the SWITCH function properly assigns values based on the hierarchy levels. Ensure the Hierarchy[Level] column values exactly match the strings in the SWITCH measure. Here’s an example of how it should look:

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

Matrix Setup:

• Rows Section: Place the Level column from your Hierarchy table in the Rows section of the matrix.
• Values Section: Add the Display Sales measure to the Values section.
• Ensure that the sorting of the hierarchy levels is correct (you might need to manually reorder it using the "Sort by Column" feature if it doesn’t match your desired sequence).

Adding Drill-Down Functionality:

Since you want to mimic drill-down behavior, you’ll need to structure the data so that the hierarchy is multi-leveled, similar to how you would with fields. Here’s an approach to implement drill-down functionality:

1. Hierarchy Drill-Down in Power BI: You can modify your hierarchy table to include separate columns for each level of the hierarchy (e.g., Level 1, Level 2), and then place them in the matrix to enable drill-down:

   • Level 1: Contains the main categories (e.g., "Net Sales").
   • Level 2: Contains the sub-categories (e.g., "Net Trade Sales", "Trade Sales").

   Example Hierarchy Table:

1. Level 1 Level 2

   Net Sales	Net Trade Sales
   Net Sales	Trade Sales
   Interco	Interco Sales

   This way, you can drag Level 1 and Level 2 into the rows of the matrix to enable drill-down.

2. Matrix Settings for Drill-Down: In the matrix visual, ensure that the drill-down option is enabled:

   • Select the matrix visual.
   • In the visual header, turn on the "Drill Down" icon (the downward arrow).
   • You can then drill down from Net Sales to Net Trade Sales or Trade Sales.

By separating the hierarchy into multiple columns, you’ll have the ability to expand and collapse different levels of the hierarchy, simulating a drill-down effect.

Verify Data in Switch:

If you’re still not seeing the correct values, ensure that your Switch function matches the Level values exactly, including any possible spacing or capitalization issues.

Let me know if this resolves the issue, or if you need further adjustments!

[123abc]

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!