Forum Discussion

suma2413's avatar
suma2413
Frequent Visitor
2 years ago
Solved

Display Measures with Custom Hierarchy

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 H...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    suma2413
    Frequent Visitor
    2 years ago

    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.

     ExpectedCurrent
    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