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

[DavidAnthony]

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 details:
I have a measure called Avg Price, which calculates the average price of a car according to each color (Color) of the car.

When at level 1 (only Color), the measure returns the exact value as expected:

Black: 28,564.80

Pale White: 27,483.89

Red: 28,736.32

➡️Figures 1 and 2 show that these values are displayed correctly according to each color.

However, when drilling down to level 2, adding Company (car manufacturer), the table now creates subrows for each manufacturer, and totals them for each color → the value is changed.

➡️Figure 3 clearly illustrates this: Black includes many brands such as Acura, Audi, BMW... and the value on the Black line is recalculated as the Sum of subrows, not the Average of all Black colors as before.

❓Question:
I want the Color line at the higher level to keep the original Average value, even though I have drilled down and displayed each brand (Company) below.

Is there a way to keep the values:

Black: 28,564.80

Pale White: 27,483.89

Red: 28,736.32

even when I have drilled down to Company based on the dataview power bi api returned as in figure 4

Best regards,

DavidAnthony

[v-veshwara-msft]

Hi @DavidAnthony ,

Following up to see if your query is resolved.
If you need further assistance, please reach out with sample .pbix to assist better.

Thank you.

[v-veshwara-msft]

Hi @DavidAnthony ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out. You could also share .pbix file without any sensitive data.
Thank you.

[SamWiseOwl]

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]

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 · Accepted solution]

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]

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.