Total Value break by columns when having multi-select in slicer

[Anonymous]

Hi all, 

Here's my data table

Month	Category	SubCategory	Publisher	Qty
Aug-21	Book	Science	Publisher A	10
Aug-21	Book	Computer	Publisher A	20
Aug-21	Book	Marketing	Publisher A	25
Aug-21	Book	Science	Publisher B	15
Aug-21	Book	Computer	Publisher B	25
Aug-21	Book	Marketing	Publisher B	25
Aug-21	Book	Computer	Publisher C	10
Aug-21	Magazine	Science	Publisher A	10
Aug-21	Magazine	Science	Publisher B	30
Aug-21	Magazine	Science	Publisher C	60
Jul-21	Book	Science	Publisher A	20
Jul-21	Book	Computer	Publisher A	15
Jul-21	Book	Marketing	Publisher A	40
Jul-21	Book	Science	Publisher B	60
Jul-21	Book	Computer	Publisher B	30
Jul-21	Book	Marketing	Publisher B	15
Jul-21	Book	Computer	Publisher C	50
Jul-21	Magazine	Science	Publisher A	50
Jul-21	Magazine	Science	Publisher B	40
Jul-21	Magazine	Science	Publisher C	20

I'm trying to get this exact result:

Month	Category	Publisher	Qty	Total	%
Aug-21	Book	Publisher A	30	80	38%
Aug-21	Book	Publisher B	40	80	50%
Aug-21	Book	Publisher C	10	80	13%

Slicer:

• SubCategory = Science, Computer (multi-select)
• Month = Aug 21

DAX:

• Total = CALCULATE(SUM([Qty]),REMOVEFILTERS([Publisher]))
• % = DIVIDE([Qty],[Total])

But im getting this instead:

the total % would be over 100%. 37.5+50+18.18=105.7%

I think the total for Publisher C is 55 due to it does not have any book in SubCategory Science, thus only able to get total 55 (which is the total of SubCategory Computer).

Can anyone help me on this? i need the Total to be 80 when i multi-select the SubCategory.

Edit:

When Im not selecting any slicer, it should look like this

- The total will break by Month and Category, so i can get 100% by each Month and Category

Thanks in advance

[Kumail]

Hello @Anonymous 

If you could send a sample .pbix that demonstrates what you are looking to get. It would really help to provide you with a quick solution. 

You can send the sample .pbix file by adding it to your drive or dropbox and add the link here. 

Regards
Kumail Raza

[V-lianl-msft]

Yes, measure is calculated according to the current row context. In your scene, after using slicer and clearing the [publisher] filter, the total of publisher C is 55.

Your formula has been checked by me and there is no problem:

[Anonymous]

Hi @V-lianl-msft 

Now I hit another problem after i applied to my actual report.

- when nothing is selected in any of the slicers, it takes very long to load or exceeded resource

- the only way to improve that is that i select something to reduce the data

Do you have other DAX that can achieve exact same result but with better performance?

my table has around 800thousands row and it is growing day by day