Show sum per category and do not change it with filter

[Ania26]

Hello, I want to have table that shows TTL Number of Packs per Year and Quarter and Destination Market.

So for Example for Australia it is 5 in Q2 and 7 in Q4.

Then when I filter by Market Variant I still want to see Destination Market Australia with all quarters and total values 5 and 7.

This will be only used in Excel, with Analyse in Excel function. So I need to create measure that works in Excel.

Thank you.

Please help.

Thank you,

Anna

Destination Market	Year	Quarter	Market Variant	TTL Number of Packs per Destination Market
AUSTRALIA	2022	Q2	PHILIPPINES	5
AUSTRALIA	2022	Q2	DUTY FREE	5
AUSTRALIA	2022	Q2	DUTY FREE	5
AUSTRALIA	2022	Q2	PHILIPPINES	5
AUSTRALIA	2022	Q2	KOREA	5
AUSTRALIA	2022	Q4	JAPAN	7
AUSTRALIA	2022	Q4	JAPAN	7
AUSTRALIA	2022	Q4	KAZAKHSTAN	7
AUSTRALIA	2022	Q4	DUTY FREE	7
AUSTRALIA	2022	Q4	PHILIPPINES	7
AUSTRALIA	2022	Q4	KOREA	7
AUSTRALIA	2022	Q4	PHILIPPINES	7

[Thejeswar]

Hi @Ania26 ,

Just removing the duplicates in your dataset will fix this. and show 5 and 7 respectively for Q2 2022 and Q4 2022 for Australia for any market filter applied.

 Set the Aggregation of TTL Number of packs to Maximum as shown below. This will show 5 and 7 as you requested, even when multiple market variant filters are applied. 

Setting this aggregation to sum will sum the TTL Number for each market variant selected. Refer below ss

Regards,

[Ania26]

Hello, This will be used in Excel so it has be be straightforward calculation for everyone who can do basic Pivot Table analysis. Thank you for your effort.

[Thejeswar]

@Ania26 ,

Got it.

Create a measure as shown below

Max_TTL = CALCULATE(MAX('Table'[TTL Number of Packs per Destination Market]))

This you can access directly from excel using Analyze in excel. But I doubt you can do anything at the DAX level to ignore the Market Unit filter that will be applied in excel.

Just like how there is edit interaction in Power BI, you might have to find a way to ignore the filters applied on market unit from within excel.

Regards,

[v-jialongy-msft · Accepted solution]

Hi @Ania26 

Please try the following measure:

Measure = 
VAR _selectyear = SELECTEDVALUE('Table'[Year])
VAR _selectquarter = SELECTEDVALUE('Table'[Quarter])
RETURN
CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Year] = _selectyear && 'Table'[Quarter] = _selectquarter))

Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ania26]

Hello, when I select Market Variant Kazakhstan, I still see only Q4:

Thank you for your help. 

[Ania26]

Hello, thank you for your response. I forgot to add, that this will be only used in Excel, with Analyse in Excel function. So I need to create measure that works in Excel.

Thank you.

[miTutorials]

Go to Format Tab and click on Edit Interactions, select the filter and on the table select no nothing.. See the video below.

How to Make a Slicer not affect all the Visuals in PowerBI | Edit Interactions | MI Tutorials (youtu...