Forum Discussion

tyagiyash32's avatar
tyagiyash32
Helper I
7 years ago
Solved

Drill down Suggestion

Hi,  the data is as shown in image:   so in a barchart on axis as region and country (drill down) & on values it shoud reflect the calculated measure, i.e. M, L1. That drill down easily show...
Need Help
v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

Hi tyagiyash32 

You may use 'unpivot column' in query editor for Units,M S...L1 C,L1 On columns.Then create measures as below.

M =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER ( Table1, LEFT ( Table1[Attribute], 1 ) = "M" )
)
    / CALCULATE (
        COUNT ( Table1[Attribute] ),
        FILTER ( Table1, LEFT ( Table1[Attribute], 1 ) = "M" )
    )
    / CALCULATE (
        SUM ( Table1[Value] ),
        FILTER ( Table1, Table1[Attribute] = "Units" )
    )

L =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER ( Table1, LEFT ( Table1[Attribute], 2 ) = "L1" )
)
    / CALCULATE (
        COUNT ( Table1[Attribute] ),
        FILTER ( Table1, LEFT ( Table1[Attribute], 2 ) = "L1" )
    )
    / CALCULATE (
        SUM ( Table1[Value] ),
        FILTER ( Table1, Table1[Attribute] = "Units" )
    )

Regards,

  • tyagiyash32's avatar
    tyagiyash32
    Helper I
    7 years ago

    Hi Cherie,

     thanks for the quick response, i did the steps mentioned by you but still measures 'M' & 'L' doesn't drills down to the lower values. can you share me a an image where 'M' & 'L' gets drilled down M S...L1 C,L1...

    Regards

    Yash

    • tyagiyash32's avatar
      tyagiyash32
      Helper I
      7 years ago

      Sorry my bad i was not considering attibutes column in axis, the chart gets drilled down to M S...L1 C,L1... now but the respective values are coming as infinity, do you have any clue how the values can be corrected? I was looking to show values as percentages.

    • v-cherch-msft's avatar
      v-cherch-msft
      Microsoft Employee
      7 years ago

      Hi tyagiyash32 

      I'm afraid i cannot fully understand the 'drill down to the lower value' .Could you explain more about your expected output?

      Regards,

      • tyagiyash32's avatar
        tyagiyash32
        Helper I
        7 years ago

        Hi, the drill down is working as shown in the image below

        but the values are coming as infinity. Do you have any clues on this, actually i want the values as percentages