Forum Discussion
How do you work with grouped data across tables?
Hi stvcdms,
You can create another table by using the DAX below.
Table = SUMMARIZE(Sales,Employee[EmpID],"TotalSales",CALCULATE(SUM(Sales[SaleValue]),ALLEXCEPT(Sales,Employee[EmpID])))And then add a calculated column
Column = 'Table'[TotalSales]-LOOKUPVALUE(Employee[PromotionSalesGoal],Employee[EmpID],'Table'[EmpID])Reference
https://msdn.microsoft.com/en-us/library/gg492171.aspx
https://msdn.microsoft.com/en-us/library/gg492170.aspxRegards,
Charlie Liao
You should be able to relate your two tables on "Emp ID". Then, you could create a new column in your Employee table, something like:
Promotion Sales = CALCULATE(SUM(Sales[Sale Value ($)]),Sales[Sale Type]="MyType")
You could then create a column or measure to subtract your values and display everything in a Table or Matrix visualization.
Thanks for the reply. I think the issue I am seeing is that the Promotion Sales Goal ($) value from Table 1 is also getting summed when it should be a single value subtracted from the aggrigate on Table 2. I am unable to reference across tables, so I can't point to the Promotion Sales Goal ($) in Table 1. When I Join it to Table 2 I am getting the sum issue.
Example
Emp ID : 01
Emp Name : Joe
Promotion Sales Goal ($) : 1000
Promotion Sales : 500
[Promotion Sales Goal ($)] - Sales[Promotion Sales] = -1500
It's summing Promotion Sales Goal ($) : 1000 when my expected result is -500.
Hi stvcdms,
You can create another table by using the DAX below.
Table = SUMMARIZE(Sales,Employee[EmpID],"TotalSales",CALCULATE(SUM(Sales[SaleValue]),ALLEXCEPT(Sales,Employee[EmpID])))And then add a calculated column
Column = 'Table'[TotalSales]-LOOKUPVALUE(Employee[PromotionSalesGoal],Employee[EmpID],'Table'[EmpID])Reference
https://msdn.microsoft.com/en-us/library/gg492171.aspx
https://msdn.microsoft.com/en-us/library/gg492170.aspxRegards,
Charlie Liao