Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi guys, i think what i'm trying to do may be simple, but i'm not finding a way to get it right. I am trying to do the equivalent of a sumif in excel. I have the following tables:
STORES | |
store | MANAGER |
A | M1 |
B | M2 |
C | M3 |
D | M4 |
w_PRODUCTS | |
PRODUCT | PROD_NAME |
P1 | ABCD |
P2 | DEFG |
P3 | GHIJ |
P4 | KLMN |
P5 | OPQR |
... | ... |
STOCK NETWORK | ||
PRODUCT | STORE | QTY |
P1 | A | 2 |
P1 | B | 1 |
P1 | C | 2 |
P1 | D | 0 |
P2 | A | 1 |
P2 | B | 1 |
P2 | C | 1 |
P2 | D | 3 |
... | .. |
and I want the following table with two collums, the first showing the stock of the product in the specific store from that row, and the second collum with the sum of everything from that product(in that row) within all the stores.
Store | product | stock (store) | stock(all stores) |
A | P1 | 2 | 5 |
B | P1 | 1 | 5 |
C | P1 | 2 | 5 |
D | P1 | 0 | 5 |
A | P2 | 1 | 6 |
B | P2 | 1 | 6 |
C | P2 | 1 | 6 |
D | P2 | 3 | 6 |
A | P3 | 3 | 7 |
B | P3 | 2 | 7 |
C | P3 | 1 | 7 |
D | P3 | 1 | 7 |
When I tried the second collum it return values from the store from the row only, not the sum from all of them
Thanks
Solved! Go to Solution.
Hi @yasmin
You should be able to create a measure thusly:
Count All Stores = CALCULATE(SUM(Stock[QTY]), ALL(Store[store]))
Hope this helps
David
@yasmin what you need to do is use all except
calculate(sum(table1[qty]), allexcept(table1, table1[product])
https://msdn.microsoft.com/en-us/query-bi/dax/allexcept-function-dax
@yasmin my bad, you need to use store instead of product in the formula
calculate(sum(table1[qty]), allexcept(table1, table1[store])
Hi @yasmin
You should be able to create a measure thusly:
Count All Stores = CALCULATE(SUM(Stock[QTY]), ALL(Store[store]))
Hope this helps
David
Thank you guys! It worked! 😃
Please share a picture of your data model - with the correct modelling the calculation should be straightfoward.
The tables are just like I presented above
You did not "present" the data showing the relationship between the tables. The best approach is a screenshot of the "model" page in your PowerBI file.
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |