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
Super User

Can you post sample data and expected results?


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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,

 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors