Forum Discussion

DavidAnthony's avatar
DavidAnthony
Helper II
1 year ago
Solved

Keep Measure value when Drilldown in Table Matrix (Do not accumulate subrows)

Hello Power BI community, I am customizing my visual I am facing a fairly common situation but do not know the optimal way to handle it, I hope everyone can support or share experiences. ✅Problem d...
Need Help
  • v-veshwara-msft's avatar
    v-veshwara-msft
    1 year ago

    Hi DavidAnthony ,

    Thanks for confirming, and as SamWiseOwl showed, ISINSCOPE is a good approach for detecting the hierarchy level. If your current measure isn’t giving the expected result, it may be due to how the filter context is being applied when drilling down in your custom visual.

     

    At the Color level, you’ll need to make sure the calculation ignores the Company filter so it matches the original average you posted. You can do this by adapting the SWITCH pattern to use REMOVEFILTERS or ALL for that level, for example:

    SWITCH(
    TRUE(),
    ISINSCOPE(Cars[make]),
        AVERAGE(Cars[value]),
    ISINSCOPE(Cars[color]),
        CALCULATE(AVERAGE(Cars[value]), REMOVEFILTERS(Cars[make])),
    BLANK()
)

    If you can share the dataset or a sample version of your measure, it will be easier to pinpoint why your current code is not aligning with the Matrix visual output.

    You could also share .pbix file without any sensitive data. Please follow this guide on how to share .pbix.: How to provide sample data in the Power BI Forum - Microsoft Fabric Community

     

    Hope this helps. Please reach out for further assistance.

    Thank you.

SamWiseOwl's avatar
SamWiseOwl
Super User
1 year ago

Hi DavidAnthony 

Is the issue occuring when you bring the data in from the source or specifically in the Power BI visuals?
I assume it is something to do with the number of items included in the average.

I've made a mock up for myself and anyone else who comes to help.
Please look at it and see if you can make it closer to the problem.

One thing you could try is using a SWITCH function to do different calculations on each line. 

Avg calculated seperate =
 SWITCH(
    TRUE() --like a nested IF

    ,ISINSCOPE(Cars[make]) --If on the make row return true
    ,AVERAGE(Cars[value])

    ,ISINSCOPE(Cars[color]) --If on the colour row (subtotal)
    ,sum(Cars[value]) / COUNT(Cars[id])

    ,"Something else" --if on the grand total return something else
 )



DavidAnthony's avatar
DavidAnthony
Helper II
1 year ago

Thanks for your help but I am having problem with my code. I want to do like matrix but I don't know how to calculate it to get the data like my post

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support
    1 year ago

    Hi DavidAnthony ,

    Thanks for confirming, and as SamWiseOwl showed, ISINSCOPE is a good approach for detecting the hierarchy level. If your current measure isn’t giving the expected result, it may be due to how the filter context is being applied when drilling down in your custom visual.

     

    At the Color level, you’ll need to make sure the calculation ignores the Company filter so it matches the original average you posted. You can do this by adapting the SWITCH pattern to use REMOVEFILTERS or ALL for that level, for example:

    SWITCH(
    TRUE(),
    ISINSCOPE(Cars[make]),
        AVERAGE(Cars[value]),
    ISINSCOPE(Cars[color]),
        CALCULATE(AVERAGE(Cars[value]), REMOVEFILTERS(Cars[make])),
    BLANK()
)

    If you can share the dataset or a sample version of your measure, it will be easier to pinpoint why your current code is not aligning with the Matrix visual output.

    You could also share .pbix file without any sensitive data. Please follow this guide on how to share .pbix.: How to provide sample data in the Power BI Forum - Microsoft Fabric Community

     

    Hope this helps. Please reach out for further assistance.

    Thank you.

    • v-veshwara-msft's avatar
      v-veshwara-msft
      Community Support
      1 year ago

      Hi DavidAnthony ,
      Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out. You could also share .pbix file without any sensitive data.
      Thank you.