Separately Conditional Filtering and Combining Data Sets on a Graph

[ACC_Tom]

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.

[Greg_Deckler]

Can you post sample data and expected results?

[ACC_Tom]

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

[v-sihou-msft · 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])))

2. Build the relationship to Dataset 2.

3. Create a calculated column like below:

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

Regards,