Gauge Chart with variable Maximum based on Filters

[JM_BEI]

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!

[v-tangjie-msft · Accepted solution]

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. 

[JM_BEI]

Hi and thanks for your reply. I haven't been able to test this solution as a truck hit our fiber optic and lost all connections to the server. I will try this solution tomorrow.

[JM_BEI]

This worked like a Charm!

Thanks for your support 🙂