Forum Discussion
Gauge Chart with variable Maximum based on Filters
Hello Everyone!
Thanks for your support and patience in advance. This is my first post and I'm trying to start working with PowerBI.
I'm trying to create a sales report that compares the actual sales value vs the forecasted sales in a Gauge chart. But I want the Gauge chart Max value to be updated based on the selection of a Date or range of dates (just years and months) and the selection of a program or multiple programs.
I have a table with the actual sales by each program.
Example data:
ProgramDate Sales Amount
| Program 1 | 2-Jan-23 | $ 2,885.53 |
| Program 2 | 2-Jan-23 | $ 1,496.12 |
| Program 3 | 2-Jan-23 | $ 1,496.12 |
| Program 1 | 2-Jan-23 | $ 145.62 |
| Program 2 | 2-Jan-23 | $ 452.07 |
| Program 3 | 2-Jan-23 | $ 438.72 |
| Program 1 | 2-Jan-23 | $ 72.23 |
| Program 2 | 2-Jan-23 | $ 480.00 |
| Program 3 | 2-Jan-23 | $ 1,320.00 |
| Program 1 | 2-Jan-23 | $ 100.55 |
| Program 2 | 2-Jan-23 | $ 35.70 |
| Program 3 | 2-Jan-23 | $ 138.80 |
| Program 1 | 2-Jan-23 | $ 1,070.64 |
| Program 2 | 2-Jan-23 | $ 700.70 |
| Program 3 | 2-Jan-23 | $ 11.57 |
| Program 1 | 2-Jan-23 | $ 72.23 |
and a table for the forecasted sales by each program.
Example data:
Program Sales Forecast YearMonth
| Program 1 | $ 450,000.00 | 2023 | 1 |
| Program 1 | $ 475,000.00 | 2023 | 2 |
| Program 1 | $ 500,000.00 | 2023 | 3 |
| Program 1 | $ 525,000.00 | 2023 | 4 |
| Program 1 | $ 550,000.00 | 2023 | 5 |
| Program 1 | $ 575,000.00 | 2023 | 6 |
| Program 1 | $ 600,000.00 | 2023 | 7 |
| Program 1 | $ 625,000.00 | 2023 | 8 |
| Program 1 | $ 650,000.00 | 2023 | 9 |
| Program 1 | $ 675,000.00 | 2023 | 10 |
| Program 1 | $ 700,000.00 | 2023 | 11 |
| Program 1 | $ 725,000.00 | 2023 | 12 |
| Program 2 | $ 400,000.00 | 2023 | 1 |
| Program 2 | $ 425,000.00 | 2023 | 2 |
| Program 2 | $ 450,000.00 | 2023 | 3 |
| Program 2 | $ 475,000.00 | 2023 | 4 |
| Program 2 | $ 500,000.00 | 2023 | 5 |
| Program 2 | $ 525,000.00 | 2023 | 6 |
| Program 2 | $ 550,000.00 | 2023 | 7 |
| Program 2 | $ 575,000.00 | 2023 | 8 |
| Program 2 | $ 600,000.00 | 2023 | 9 |
| Program 2 | $ 625,000.00 | 2023 | 10 |
| Program 2 | $ 650,000.00 | 2023 | 11 |
| Program 2 | $ 675,000.00 | 2023 | 12 |
| Program 3 | $ 440,000.00 | 2023 | 1 |
| Program 3 | $ 465,000.00 | 2023 | 2 |
| Program 3 | $ 490,000.00 | 2023 | 3 |
| Program 3 | $ 515,000.00 | 2023 | 4 |
| Program 3 | $ 540,000.00 | 2023 | 5 |
| Program 3 | $ 565,000.00 | 2023 | 6 |
| Program 3 | $ 590,000.00 | 2023 | 7 |
| Program 3 | $ 615,000.00 | 2023 | 8 |
| Program 3 | $ 640,000.00 | 2023 | 9 |
| Program 3 | $ 665,000.00 | 2023 | 10 |
| Program 3 | $ 690,000.00 | 2023 | 11 |
| Program 3 | $ 715,000.00 | 2023 | 12 |
I currently have some slicers fot the year and month that filters a table that shows the sales data for each program and a Gauge that changes the sales values based on that selection but the Max value which is set as the sum of forecast in Forecast table its not being filtered.
I would also like the max value to be changed if a single program is selected or is multiple programs are selected.
Thanks!
Hi JM_BEI ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create a measure.
max = CALCULATE(SUM('Forecast table'[Sales Forecast]),FILTER(ALLSELECTED('Forecast table'),'Forecast table'[Sales Forecast]))(3) Place the measure in the max value field.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi JM_BEI ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create a measure.
max = CALCULATE(SUM('Forecast table'[Sales Forecast]),FILTER(ALLSELECTED('Forecast table'),'Forecast table'[Sales Forecast]))(3) Place the measure in the max value field.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
