Dynamic Measures - Visualisation based on highest value

[Daniel_B]

Hi All

In my head this sounds very ambitious and I'm sure it's advanced level PBI skill requirements but I wanted to know 2 things based on what I am trying to achieve which is:

Challenge: Create a measure that will automatically update the values and category if that value is higher than the others in it's category

For example, I am measuring an average 5* rating score across 4 sites and would like to have a measure/DAX which will show whichever one has the highest average without changing filters/slicers or anything else on the data so when new data comes in, if site 1 was last months #1 and this month site 4 has pulled a comeback then it should show in the visualisation I want it to show in (Gauge Chart)

1. How difficult is this? Am I overthinking it or is it really complicated

2. Are there any limitations on this idea other than skill if Q1 yields a "very difficult to do" response

If anyone has any suggestions, examples or links to useful videos/tutorials I would greatly appreciate the chance to bring this to life

Thanks

Dan

[Anonymous]

Hey @Daniel_B ,

There are probably other ways to do this, however this was my solution:

1. Create a measure to calculate the average rating per site
2. Create another measure to calculate the maximum of the average ratings
3. Create a calculated table, filtered to show only the row where Average Rating = Max Rating
4. Use the calculated table to populate the guage & label (I use a card showing only the Site Name)

Tables

xAvgRating = AVERAGEX(Ratings, Ratings[Rating])

xMaxRating = 
CALCULATE(MAXX(Ratings, Ratings[xAvgRating]), ALL(Ratings))

tMaxVal = 
FILTER(Ratings, [xMaxRating] = [xAvgRating])

Table and Visual Output

Cheers,

Andy