cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ACC_Tom
New Member

Separately Conditional Filtering and Combining Data Sets on a Graph

Hi Guys, new user here.

 

What would be the best way to simultaneously separately filter and combine two datasets visually?

 

I have a table of invoiced amounts that I wish to filter post a specific date and sum by month.

 

I also have a table of projects with forecast amounts that need to be filtered depending on job status to exclude those with specific statuses (dead, unsuccessful ...) and then summed with the invoiced values by month and displayed graphically.

 

Is it possible to separate filters and combine within the graphic display options or am I looking at pre-filtering and combining the data into a new table, column, etc.

 

Thanks in advance for your assistance.

1 ACCEPTED SOLUTION

@ACC_Tom

 

In this scenario, to combine two datasets data together, you should aggregate the rows in your Dataset 1 first. Then build the relationship to Dataset 2. Then you can just create a calculated column combine 'Invoice Amount' and 'Value'.

 

1. Create a table aggregating the rows in Dataset 1.

 

Table = SUMMARIZE(Table3,Table3[Client],Table3[Date],"Amount",CALCULATE(SUM(Table3[Invoice Amount])))

222.PNG

2. Build the relationship to Dataset 2.

333.PNG

3. Create a calculated column like below:

Total Value = 'Table'[Amount]+RELATED(Table4[Value])

444.PNG

 

Regards,

 

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User IV
Super User IV

Can you post sample data and expected results?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Dataset 1

ClientInvoice AmountDate
X200Jan
X1000Jan
Y400May
Z600

Oct

 

Dataset 2

ProjectValueStatusEst. Completion Date
A450To DoJan
B900To InvoiceMay
C1000CompleteOct

 

 

What I am trying to do is display the total amount from invoice and forecasted work on a month by month basis.  However I to filter Dataset 2 by Status, in this case to remove "Complete" entries.  The two datasets share a common identifier code column to link them if needed.

 

The end result for the above example would be:

 

MonthValue
Jan1650
May1300
Oct600

@ACC_Tom

 

In this scenario, to combine two datasets data together, you should aggregate the rows in your Dataset 1 first. Then build the relationship to Dataset 2. Then you can just create a calculated column combine 'Invoice Amount' and 'Value'.

 

1. Create a table aggregating the rows in Dataset 1.

 

Table = SUMMARIZE(Table3,Table3[Client],Table3[Date],"Amount",CALCULATE(SUM(Table3[Invoice Amount])))

222.PNG

2. Build the relationship to Dataset 2.

333.PNG

3. Create a calculated column like below:

Total Value = 'Table'[Amount]+RELATED(Table4[Value])

444.PNG

 

Regards,

 

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors