Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
Thanks a million this helped a lot and solved the issue.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |