POWERBI: SUM IF BETWEEN TABLES

[yasmin]

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

[Anonymous]

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

Here is the data model @dedelman_clng

DATA

@Anonymous when i use all except this happens:

ALLEXCEPT

[Anonymous]

@yasmin my bad, you need to use store instead of product in the formula

calculate(sum(table1[qty]), allexcept(table1, table1[store])

[dedelman_clng · Accepted 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]

Thank you guys! It worked! 😃

[dedelman_clng]

Please share a picture of your data model - with the correct modelling the calculation should be straightfoward.

[yasmin]

The tables are just like I presented above

[dedelman_clng]

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.