Needed Measures to calculate total Supply and Gap

[Anonymous]

Hi,

I needed help on to build 2 measures on my data which screenshot is mentioned below.

In First, Demand Table has demand against each product. Three store tables (Store1,Store2 and Store3) has supply nos. for each product.

Now, I needed 2 measurs. 

"Total Supply" = Unit1Supply+Unit2Supply+Store2Supply+Store3Supply)

 "Demand-Supply Gap" =Total Supply- Demand

Demand
Product	Demand
A	2000
B	3500
C	1500
D	800

Store1
Product	Unit1Supply	Unit2Supply
A	500	400
B	400	200
C	350	125
D	350	400

Store2
Product	Supply
A	600
B	1200
C	213
D	150

Store3
Product	Supply
A	800
B	300
C	167
D	300

Measure1	Measure2
Total Supply	Demand-Supply Gap

Thanks

@Greg_Deckler @MFelix 

[Greg_Deckler · Accepted solution]

Well, first, this would be far easier if you just Appended your 3 store tables. That being said here is some untested code:

Total Supply Measure = 
  VAR __Store1Unit1Supply = SUMX(RELATEDTABLE('Store1'),[Unit1Supply])
  VAR __Store1Unit2Supply = SUMX(RELATEDTABLE('Store1'),[Unit2Supply])
  VAR __Store2Supply = SUMX(RELATEDTABLE('Store2'),[Supply])
  VAR __Store3Supply = SUMX(RELATEDTABLE('Store3'),[Supply])
RETURN
  __Store1Unit1Supply  + __Store1Unit2Supply + __Store2Supply + __Store3Supply

Demand-Supply Gap Measure =
  [Total Supply Measure] - SUM('Demand'[Demand])

[amitchandak]

Create a common product table and join with all four tables

After that you can have measure like

Correct the table names and measure names

Total Supply = sum(Unit1[Supply])+Sum(Unit2[Supply])+sum(Store2[Supply])+sum(Store3[Supply]))

 "Demand-Supply Gap" =[Total Supply]- sum(Demand[Demand])

[Anonymous]

Thanks for the prompt reply. It worked for me.

[Greg_Deckler]

Sweet! 🙂