Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
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 |
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 ) ) )
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] ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |