Sum total from 2 different tables and total value change according to slicer option

[yasemsem]

I have 2 different tables

Pipeline
Forecast Category	Quarter	Total
Best Case	FY23-Q3	$
Commit	FY23-Q4	$
Pipeline

Bookings
Forecast Category	Quarter	Total
Booked	FY23-Q3	$
	FY23-Q4	$

The tables have many different columns but those are the one I need for this question.

I want to have a card or barchart showing the value of both Bookings and Pipeline tables and it changes according to slicer value for the forecast category and quarter.

I tried the below:

1. creating a measure: sum('Bookings'[Total]) + sum('Pipeline'[Total]) --> here total doesn't add up correctly when I change the slicer value as the measure always takes the bookings even if it was not chose so the final result is always wrong.

2. Tried to append the 2 tables.. but it messed up other tables and I couldn't understand why.

3. SelectedValue only works when I am selecting one option from the filter but if I want to select Pipeline, Commit and booked.. it cannot be applied here.

Appreciate your help and please let me know if any information is needed.

[yasemsem]

Thanks a million this helped a lot and solved the issue.

[v-yadongf-msft · Accepted solution]

Hi @yasemsem ,

The reason why you can't append the table directly maybe that number of columns of the two tables are not equal. Please try following DAX to create a new table:

Table = UNION(
    SELECTCOLUMNS('Bookings',"Forecast Category",[Forecast Category],"Quarter",[Quarter],"Total",[Total]),
    SELECTCOLUMNS('Pipeline',"Forecast Category",[Forecast Category],"Quarter",[Quarter],"Total",[Total])
)

You will get a table like this:

Create a measure:

Measure = SUM('Table'[Total])

The total value will be changed according to slicer value for the forecast category and quarter.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.