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

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

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.


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.