How do you work with grouped data across tables?

[stvcdms]

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?

[Greg_Deckler]

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.

[stvcdms]

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.

[v-caliao-msft · Accepted solution]

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.aspx

Regards,

Charlie Liao