ALL works for one formula but not the other

[dapperscavenger]

I have a matrix visual like this:

When you click on one of the boxes, it brings up a table below of the top ten products (by inventory volume) that are sitting in that category right now (this month).  The idea is for people to click on the red and amber boxes, to see what their problematic inventory is. The % figures show proportion of overall stock in each category and give a idea of overall portfolio health.

I've been asked to include a MoM change to the table, so people can see how the inventory volume changes this month vs last month, but they don't want to see the change in just that category (box); they want to see total MoM change for all stock of the product of which any volume is sitting in that category.

What is happening at the moment is that I can't seem to filter the calculation so that I can see both the volume only in that selected box, and the total volume as well.

e.g. here is the current position for one product:  

So both batches reduced in quantity vs. last month, but both batches also moved categories.  The risk matrix shows the % of stock in each category this month:

When I look at all the boxes (no filter on matrix) I get the correct result:

When I click on the AY box only, I get the below result:

The Selected box = 308 (correct) because that is the total of volume in the selected box in the matrix.  The All risks is supposed to show  418 (incorrect), as the total of all quantity on that SKU regardless of box.

The Last Month (LM all risks) seems to be working as well

The calculations are as below:

All risks = CALCULATE([UoM TM Market],ALL(Inventory[Depletion in 4 months], Inventory[Risk Group 1]))

LM All Risks = CALCULATE([UoM LM Market],ALL(Inventory[Depletion in 4 months], Inventory[Risk Group 1]))

Where All risks comes from:

TM Market = CALCULATE(

SWITCH(TRUE(),

VALUES(SwitchUoM[SwitchUoM]) = "Value GBP", [Sum Value Market],

VALUES(SwitchUoM[SwitchUoM]) = "Volume AC", [Sum Volume AC Market],

VALUES(SwitchUoM[SwitchUoM]) = "Volume EU", [Sum Volume EU Market],

VALUES(SwitchUoM[SwitchUoM]) = "No. of SKUs", [Sum Distinct SKUs Market],

[Sum Volume 9L Market])

, FILTER(ALL(Inventory[InventoryDate]) , Inventory[InventoryDate] = MAX(Dates[Date]))

)+0

So I'm really confused why it's not working

[v-xuding-msft]

Hi @dapperscavenger ,

Can you please share some sample data and your expected results with us? We will understand more clearly. Thanks.