cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yasemsem
Frequent Visitor

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

I have 2 different tables

Pipeline  
Forecast Category QuarterTotal
Best CaseFY23-Q3$
CommitFY23-Q4$
Pipeline  

 

Bookings  
Forecast Category QuarterTotal
BookedFY23-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.

1 ACCEPTED SOLUTION
v-yadongf-msft
Community Support
Community Support

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:

vyadongfmsft_0-1668049625627.png

 

Create a measure:

 

Measure = SUM('Table'[Total])

 

 

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

vyadongfmsft_1-1668049708207.png

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.

View solution in original post

2 REPLIES 2
yasemsem
Frequent Visitor

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

v-yadongf-msft
Community Support
Community Support

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:

vyadongfmsft_0-1668049625627.png

 

Create a measure:

 

Measure = SUM('Table'[Total])

 

 

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

vyadongfmsft_1-1668049708207.png

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors