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

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

3 Replies

  • 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