Display Measures with Custom Hierarchy

[suma2413]

Hello,

I am working on converting an Excel report to a powerBI report. 

We have calculated multiple measures separately calculated for each row displayed below, but we now have to display it in a Heirarchy something like below.

All Sales
       Sales in transit
                By Road
                By Air
       Sales in Destruction
                By Theft
                By Fire

I have created a custom hierarchy table for the rows but I am unable to figure out how to display each measure front of the value. Any DAX solution for the same?

Thank you in advance

[Anonymous · Accepted solution]

Hi  @suma2413 ,

I created some data:

Are you referring to custom rules in each hierarchy? You can use the IF() + Isinscope() functions to determine each hierarchy.

Here are the steps you can follow：

1. Create measure.

Measure =
IF(
    ISINSCOPE('Table'[Group2]),1,
    IF(
         ISINSCOPE('Table'[Group1]),2,
        IF(
           ISINSCOPE('Table'[ALL Sales]),3,0)))

2. Result:

Best Regards,

Liu Yang

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

[suma2413]

Hello,

This has helped a bit but my scenario is a little different. We have seperate measure calculated for each category due ot multiple conditons. I have used the below DAX and have mentioned the expectations. Kindly let me know what am I missing.

DAX: 

Final Value =

VAR Lev2= SWITCH(SELECTEDVALUE('Entity Hierachy'[Level2])
,"Sales in transit",[Transit Measure]
,"Sales in Destruction",[Destruction Mesure]
)
VAR Lev3 = SWITCH(SELECTEDVALUE('Entity Hierachy'[Level3])
,"By Road",[Road Measure]
",""By Air"",[Air Mesure]
"
,"By Theft",[Theft Measure]
",""By Fire"",[Fire Mesure]
"
)

VAR Result =
IF(ISINSCOPE('Entity Hierachy'[Level2]),Lev2,
IF(ISINSCOPE('Entity Hierachy'[Level3]),Lev3,[All Sales]))

RETURN Result

When Levl 1 is expanded all good( level 2 values displayed correctly), but when I expand any of the Level 2, Below is the output.

	Expected	Current
All Sales        Sales in transit                 By Road                 By Air        Sales in Destruction                 By Theft                 By Fire	30000 20000 12000   8000  10000   3000   7000	30000 20000 20000 20000 10000 10000 10000

[Ritaf1983]

Hi @suma2413 

provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523