Forum Discussion
Subtraction between two columns from table after grouping
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
5 Replies
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 YuSo...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.
Hi Anonymous,
Please download my pbix file attached in previous post. It should work on you side as well.
Best Regards,
Qiuyun Yu
