How to create a new measure from two calculated tables?

[mallap849]

Hello Everyone,

I have two different sources of data (Files are attached below).  – Table 1 and Table 2. Table 1 has the actual expense for the month i.e., TRANS VALUE. The expenses happen on different days of the month i.e., TRANS_DATE.

Table 2 has the budget by month and year so I create a new variable Date.

I first aggregate the two tables to Table 1a and Table2a so that I can get the Actual (from Table 1) and Budget (from Table 2) by Org.

However, I could not create a VAR and %VAR

Desired Output:

 Thank you, in advance for all the help & suggestions.

Excel File 

Power BI File 

[v-weiyan1-msft · Accepted solution]

Hi @mallap849 ,

Based on the sample and description you provided,
Please try code as below to create two measures.

VAR = SELECTEDVALUE('Table2a'[Budget-FY'23]) - SUMX('Table1a',[EXP])

%VAR = 
var Total_EXP =  SUMX('Table1a',[EXP])
return
IF(Total_EXP, Total_EXP / SELECTEDVALUE('Table2a'[Budget-FY'23]),0)     

Result is as below.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mallap849]

Thank you, @v-weiyan1-msft . This works.

[mallap849]

Hello Everyone,

Please try using the following link. Hopefully this works.

Thank you,

Link to the file 

Power BI

https://drive.google.com/file/d/16M4-cWDPnDcS6iW3unfEI6-AM2tx5Tcl/view?usp=drive_link

Excel

https://docs.google.com/spreadsheets/d/1GI9liZ7_PhGvGLYScle3oLH-pjqJt7yC/edit?usp=sharing&ouid=10228...

[gadielsolis]

Hey, looks like the links for the attached documents are not working properly

[mallap849]

https://www.dropbox.com/scl/fi/ywlsmgnjg7rkvtr09u1rb/Sample1.pbix?rlkey=qcxwp9m7jyxtg832b07rp1u9j&dl... 

[mallap849]

@gadielsolis  does the above link work? LMK Thank you!