Subtraction between two columns from table after grouping

[Anonymous]

Hi So I have two tables As below :

Table 1

Role     Foercast

1             15

1              23

2              11

1              13

Table 2

Role   Delivered

1            10

1            12

2            10

2             1

My result should be a table where i sum up the Role values from each table and have difference between the forecast and Deliverd column

Role  Difference(foercast-delieverd)

1             28

2             0

. I tried creating two tables by DAX to sum up Roles value and then merging them based on Role but when I try to do merge I dont see the tables I have created by DAX.

Please let me know how to achieve this. Thanks

[v-qiuyu-msft]

Hi @Anonymous,

Please download my pbix file attached in previous post. It should work on you side as well. 

Best Regards,
Qiuyun Yu 

[v-qiuyu-msft]

Hi @Anonymous,

You can create a measure below: 

Difference = var del=CALCULATE(SUM(Table2[Delivered]),FILTER(ALLSELECTED(Table2),'Table2'[Role]=MAX(Table1[Role])))
var Fore=CALCULATE(SUM(Table1[Forecast]),FILTER(ALLSELECTED(Table1),'Table1'[Role]=MAX(Table1[Role])))
return 
fore -del

Best Regards,
Qiuyun Yu 

[mmace1]

So...what he said ^

But alternatively - do you want the unsummarized data for some reason?  You could instead in the query, group each table by the role.   Ignore the data, I'm just highlighting where the option is in the query editor. 

Then link the two tables, and do your math via a simple related() and the substraction.  

[Anonymous]

I did this but when I go to relate, only the initial available table is seen on the Query and hence I am unable to relate

[mmace1]

Sorry, I'm not quite understanding. 

So - here's an example file where I did my idea - if this isn't correct (likely) - where is my misunderstanding? 

https://ufile.io/utz5p

Thanks,