Forum Discussion
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.
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])))
2. Build the relationship to Dataset 2.
3. Create a calculated column like below:
Total Value = 'Table'[Amount]+RELATED(Table4[Value])
Regards,
3 Replies
Can you post sample data and expected results?
Dataset 1
Client Invoice Amount Date X 200 Jan X 1000 Jan Y 400 May Z 600 Oct
Dataset 2
Project Value Status Est. Completion Date A 450 To Do Jan B 900 To Invoice May C 1000 Complete Oct 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:
Month Value Jan 1650 May 1300 Oct 600 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])))
2. Build the relationship to Dataset 2.
3. Create a calculated column like below:
Total Value = 'Table'[Amount]+RELATED(Table4[Value])
Regards,
