Forum Discussion
How do you work with grouped data across tables?
Hello,
I have the following problem that I'd like to solve with DAX.
Table 1 - Employee
Emp ID
Emp Name
Promotion Sales Goal ($)
Table 2 - Sales
Sale ID
Sale Item
Sale Type
Sale Value ($)
Emp ID
I need to group Table 2 - Sales by Emp ID and a particular Sale Type and then subtract the sum of Sale Value from Promtion Sales Goal to get an over/under for that employee.
This is pretty straight forward to do by making a new table, but I also need to mix the resulting over/under value against the sum of the other sales of different types. So where that emp is to their promotion goal and what other sales they've done. This data is ideally displayed in a pivot table/matrix.
Is it possible to do this with a DAX measure without making another table? I strikes me as the easiest way to possibly solve this with the required pivot table/matrix, but maybe it's easier to make another table and do some joins?
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
3 Replies
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
