cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mak1176 Frequent Visitor
Frequent Visitor

How to sum the rows excluded by the slicer?

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]))

 

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

 

Can anyone help?

 

Thanks!

4 REPLIES 4
mattbrice Senior Member
Senior Member

Re: How to sum the rows excluded by the slicer?

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] ) )
)
v-huizhn-msft Super Contributor
Super Contributor

Re: How to sum the rows excluded by the slicer?

Hi @mak1176,

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])


2.PNG

Best Regards,
Angelia

mak1176 Frequent Visitor
Frequent Visitor

Re: How to sum the rows excluded by the slicer?

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.

 

 

StoreProductProduct Qty
Aladder1
Bcart1
Cladder1
Dladder1
Ecart1
Fcart1
mattbrice Senior Member
Senior Member

Re: How to sum the rows excluded by the slicer?

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 ) )
)