cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## 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?

1 ACCEPTED SOLUTION
Employee

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])

Regards,

Charlie Liao

3 REPLIES 3
Super User

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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.

Employee

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])

Regards,

Charlie Liao

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors