Page filter and filter within measure on the same categorical column not working

[Anonymous]

Hi all,

I've got the data equivalent of the following:

Item	Color	Pants yes/no
Pants	Green	1
pants	Red	1
Pants	Green	1
Shirt	Green	0

I want to calculate how many pants+color combination are sold per customer (obtained from another table). I first calculate totals per color:

Green Pants = 
CALCULATE(SUM(Table[Pants yes/no]), Table[Color] = "Green")

Then the ratio

Green Pants Ratio = DIVIDE([Green Pants],[Customer Count],0)

Repeat for red. Then I add both red and green ratios to a stacked bar chart. So far so good.

The issue appears when I try to create a page filter for Pants = Green, and wanting the stacked bar chart to ONLY show the GREEN PANTS ratios. Instead, the green pants ratio is unaffected because I assume the measure filter overrides the page filter.

Potential workaround is a total [pants per customer] measure and filtering that measure by page filter colors, but then I lose the categorical stacked bar view.

Can you help me? Would be much appreciated!

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

May be you could use max() replaces allexpect() as follows:

Pants = DIVIDE(CALCULATE(SUM([Pants yes/no]), FILTER('Table',[Color]=MAX([Color]))),DISTINCTCOUNT('Table (2)'[Customer]),0) 

if you want to use multiple classification conditions, you can use "&&". for example,

FILTER('Table',[Color]=MAX([Color])&&[stores]=max([stores]))

Best Regards,

Community Support Team_ Yalan Wu

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

[Anonymous]

Thats done the trick, thanks!

[Anonymous]

Hi @v-yalanwu-msft, thank you, that answer is a solution to the problem I originally posted, but I also need to filter the results by multiple other categories (i.e. which stores, all stores within a specific region, all stores within a specific country etc). Using the allexcept nullifies the use of those filters I believe. Are there workarounds for this save for simply adding all the filters I need to allexcept?

Unfortunately I cannot share the file as it's confidential information and it is too large for anonimization.

[v-yalanwu-msft]

Hi @Anonymous  ,  

You could create a measure by the following formula:

Pants = 
DIVIDE(CALCULATE(SUM([Pants yes/no]), ALLEXCEPT('Table','Table'[Color])),DISTINCTCOUNT('Table (2)'[Customer]),0)

The final output is shown below:  

And create a stacked bar chart, Pants as Values, and color as Legend ;

When you filter Pants = Green, Pictures are as follows：

The above is my understanding, I am not very sure whether your needs are met, if I understand wrong, please share more details or remove the pbix of sensitive data.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.