Repeating subtotal for a filtered measure

[ivvan_smo]

Hi all, 

I wanted to ask, if you could help me with the issue below. 

I have similar power pivot with a measure, and I am looking for measure 2 (showing subtotals of the category for each row, but being able to filter in the pivot and measure will adjust):

Continent	Country	City	Measure	Measure 2		Continent	Country	City	Measure	Measure 2
Europe	UK	London	300	450		Europe	UK	London	300	400
Europe	UK	Leeds	100	450		Europe	UK	Leeds	100	400
Europe	UK	Birmingham	50	450		Subtotal			400	400
Subtotal			450	450	-->	Europe	Italy	Rome	75	305
Europe	Italy	Rome	75	305		Europe	Italy	Pisa	80	305
Europe	Italy	Pisa	80	305		Europe	Italy	Milan	150	305
Europe	Italy	Milan	150	305		Subtotal			305	305
Subtotal			305	305

I can create measure 2 with using all() and disregarding "City" column in the filter. However, if I filter out city  (as second table) in the pivot, measure 2 will not change as I have already excluded "City" in the filter. Can someone help? 

Thanks a lot, 

Ivan

[Greg_Deckler]

@ivvan_smo Not sure if I am 100% following but first, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[ivvan_smo]

Hi @Greg_Deckler , 

thanks a lot, but unfortunately this is not an issue with measure totals (but I have used your advices for other projects earlier, thanks for that :)). 

Here the issue is, that I would like to repeat subtotals in each row of my pivot table (using DAX). 

For measure 2 (from above) I have used following command: 

measure 2= calculate(sum(table.variable XY), all(table.[city]) - this works perfectly as long as I do not filter in the pivot. As soon as I filter out some city, the measure two does not change to the value of new subtotal (e.g. excluding Birmingham and stay 450). 

Is there some workaround? I guess it has something to do with filters. 

Hope now it is more clear. 

Thanks, 

I.