How to sum the rows excluded by the slicer?

[Anonymous]

I am trying to sum the product qty of those stores that were filtered out by the slicer.  For example, my slicer is set to show Store A quantity, which would be 1.  I would like to calculate the sum of the other stores that were sliced out.  I created a new column that copies Product Qty and named the column "ProductQtyAll".   Then I used the following:

OTHER STORE QTY = CALCULATE(SUM(Table[ProductQty], ALLEXCEPT(Table, Table[ProductQtyAll]))

But for some reason this DAX is increasing the other store Quantities by x-number..   

Can anyone help?

Thanks!

[v-huizhn-msft]

Hi @Anonymous,

As I tested, the formulas @mattbrice posted are right. You can also create the measure using the formula.

Other_store_Qty = SUMX(ALL('Table'),'Table'[Product Qty])-SUMX(ALLSELECTED('Table'),'Table'[Product Qty])

Best Regards,
Angelia

[Anonymous]

Angelia & Mattbrice, thank you for both of your responses and solutions.  One last thing.

What if you added a third column, which would be the Product.  You have a slicer for both store and Product, but you wanted to keep the slicer for Product so that the query would report the Qty of all other stores that carried ladder?

So the Slicer is set to store A and ladder.

Store	Product	Product Qty
A	ladder	1
B	cart	1
C	ladder	1
D	ladder	1
E	cart	1
F	cart	1

[mattbrice]

Not sure I 100% undertand what you want, but you can give this a try:

Sum Qty Except :=
CALCULATE (
    [Sum of Qty],
    EXCEPT ( ALL ( Table ), ALLSELECTED ( Table ) )
)

[mattbrice]

The DAX your wrote says to remove all the filters on all the columns except for Table[ProductQtyAll].  Depending on what you put on the rows or columns, this probably will return the wrong result. 

Depending on application, these may be what you want:

Sum of Qty:=SUM( Table[Qty] )

Sum Qty Except :=
CALCULATE (
    [Sum of Qty],
    EXCEPT ( ALL ( Table[Store] ), ALLSELECTED ( Table[Store] ) )
)

or this one:

Sum Qty Except :=
CALCULATE (
    [Sum of Qty],
    EXCEPT ( ALL ( Table[Store] ), VALUES ( Table[Store] ) )
)