Forum Discussion

Daniel_B's avatar
Daniel_B
Helper II
6 years ago

Dynamic Measures - Visualisation based on highest value

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

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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