Drill up in matrix table showing subtotal values even after turning them OFF

[ksivaganesh99]

Hi all,

I created a matrix visual to have a hierarchial data (all are text values in my case) as shown below. I just want last row header(last level) corresponding values in the value section which is working as expected but when I click on drill up button sub total is shown at  level 2  (level 3 is the last level). I tried turning off subtotal but no use. Can someone please help me in fixing this?

Below image with all the three level expanded. Lat levelhas values added to the values section of matrix visual.

Below image is drill up by one level. I don't want level 3 to be summarized to level 2 values. Itried turning off subtotals but no use.

Any help is highy appreciated. Thank you.

[v-lid-msft · Accepted solution]

Hi @ksivaganesh99 ,

We can try to use the following measure to meet your requirement:

Values Measure = IF(ISINSCOPE('Table'[Level3]),VALUES('Table'[Value]),BLANK())

If you have multi values in last level, please try to use FIRSTNOBLANK() function instead VALUES()

By the way, PBIX file as attached.

Best regards,

[Greg_Deckler]

Might be able to get around it using something like: 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

or 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907