Gauge Chart Help - Dynamic Maximum Value

[jgoodwin10]

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

[RodrigoTXRA]

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

[Vvelarde]

@jgoodwin10

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

[jgoodwin10]

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

[Vvelarde · Accepted solution]

@jgoodwin10

HI, create a relationship between the tables.

You can change the slicer to use The Name of the 3rd Table.

Regards

Victor

[jgoodwin10]

Perfect! Thanks for your help!