Forum Discussion
Separately Conditional Filtering and Combining Data Sets on a Graph
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,
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,