Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
stvcdms
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

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])
Capture.PNG

 

Reference
https://msdn.microsoft.com/en-us/library/gg492171.aspx
https://msdn.microsoft.com/en-us/library/gg492170.aspx

 

Regards,

Charlie Liao

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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])
Capture.PNG

 

Reference
https://msdn.microsoft.com/en-us/library/gg492171.aspx
https://msdn.microsoft.com/en-us/library/gg492170.aspx

 

Regards,

Charlie Liao

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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