Forum Discussion
Gauge Chart Help - Dynamic Maximum Value
Hi All,
I'll try and explain this as best as possible!
I am currently creating a training dashboard for moniotring the gym loading of athletes.
I have two tables. The first table is a database listing what the athlete completes during the training session including the reps and the weight.
| Name | Position | Age | Exercise | Date | Reps | Sets | Max Weight |
| James | Forward | U19 | Squat | 24.08.2017 | 5 | 5 | 100 |
| James | Forward | U19 | Bench Press | 24.08.2017 | 5 | 5 | 80 |
| James | Forward | U19 | Press Up | 24.08.2017 | 10 | 4 | 10 |
| James | Forward | U19 | Deadlift | 24.08.2017 | 5 | 5 | 100 |
| James | Forward | U19 | Bench Row | 24.08.2017 | 10 | 4 | 80 |
| Jeff | Defence | Senior | Squat | 21.08.2017 | 5 | 5 | 75 |
| Jeff | Defence | Senior | Bench Press | 21.08.2017 | 5 | 5 | 65 |
| Jeff | Defence | Senior | Press Up | 21.08.2017 | 10 | 4 | 5 |
| Jeff | Defence | Senior | Deadlift | 21.08.2017 | 5 | 5 | 90 |
| Jeff | Defence | Senior | Bench Row | 21.08.2017 | 10 | 4 | 70 |
| Jim | Defence | Senior | Squat | 21.08.2017 | 5 | 5 | 75 |
| Jim | Defence | Senior | Bench Press | 21.08.2017 | 5 | 5 | 65 |
| Jim | Defence | Senior | Press Up | 21.08.2017 | 10 | 4 | 10 |
| Jim | Defence | Senior | Deadlift | 21.08.2017 | 5 | 5 | 110 |
| Jim | Defence | Senior | Bench Row | 21.08.2017 | 10 | 4 | 65 |
The second table has target weights for each exercise based on age group and position.
| Position | Age | Exercise | Target |
| Forward | Senior | Squat | 120 |
| Defence | Senior | Squat | 110 |
| Forward | U19 | Squat | 90 |
| Defence | U19 | Squat | 80 |
The dashboard page is filtered by the player name. I would like to use a guage chart for each exercise to look at the weight the athlete lifts on a given day with the "maximum value" populated on the guage chart as the target from table two.
Does anyone know if there is an appropriate solution to this problem?
Many thanks,
James
HI, create a relationship between the tables.
You can change the slicer to use The Name of the 3rd Table.
Regards
Victor
5 Replies
Hi, try doing this:
1. In Edit Query create a IDColumn (The Same in both Tables):
Close & Apply
2. Related the tables using the IDColumn (Both Direction)
3. Create the visuals
and Finally the Gauge Chart
Hope this can help you
Regards
Victor
Lima - Peru
Hi Victor,
Thanks for your explanation and help! Apologies I made a mistake in my question. There is a third table which contains the indvidual athlete information so the 3 tables looks as follows:
Name Exercise Date Reps Sets Max Weight James Squat 24.08.2017 5 5 100 James Bench Press 24.08.2017 5 5 80 James Press Up 24.08.2017 10 4 10 James Deadlift 24.08.2017 5 5 100 James Bench Row 24.08.2017 10 4 80 Jeff Squat 21.08.2017 5 5 75 Jeff Bench Press 21.08.2017 5 5 65 Jeff Press Up 21.08.2017 10 4 5 Jeff Deadlift 21.08.2017 5 5 90 Jeff Bench Row 21.08.2017 10 4 70 Jim Squat 21.08.2017 5 5 75 Jim Bench Press 21.08.2017 5 5 65 Jim Press Up 21.08.2017 10 4 10 Jim Deadlift 21.08.2017 5 5 110 Jim Bench Row 21.08.2017 10 4 65 Position Age Exercise Target Forward Senior Squat 120 Defence Senior Squat 110 Forward U19 Squat 90 Defence U19 Squat 80 Name Position Age James Forward U19 Jeff Defence Senior Jim Defence Senior This there still a way I can do this using the method you've described below?
Many thanks,
James
HI, create a relationship between the tables.
You can change the slicer to use The Name of the 3rd Table.
Regards
Victor
Hi All
I have a similar issue and would love some feedback on it.I created a measure for my Max Value Gauge where it looks to see if there is an error when there is zero value.
MAX NEW WINS BDR = IFERROR(([SUM OF NEW WINS MonthlyBDR]*0.20),0) + IFERROR([SUM OF NEW WINS MonthlyBDR],0)
It is working fine as long as the value is greater than 1 or is zero, however, when it is equal to 1, I'm unable to see my target value as per calculation above, it will add 20% above the actual value.Any ideas on how to make this dynamic calculation also work when the Actual value is 1?
https://community.powerbi.com/t5/Desktop/Gauge-chart-dynamic-value/td-p/450873
