Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Scatter plot average line

Hi everyone, I need your help urgently ;_:

 

I am trying to get an average line that takes the average of the dot points shown in the scatter plot. I'm using a combination of slicers and SWITCH formula. 

 

 

The problem would be in the average line for Average Risk Rating Filter. When I click Reward 2, it gives me the correct average line of 1.92. But when I click Reward 1, it still gives me the average line of 1.92. It should give me 1.67, which is the average risk rating of the 2 points shown in the scatter plot (Project 1 and 2, because Project 3 and 4 do not have Reward 1 value). 

 

Average Reward 1 = AVERAGE(Sheet1[Reward 1])
Average Reward 2 = AVERAGE(Sheet1[Reward 2])
Reward Selection = SWITCH( TRUE(), VALUES('Dynamic Reward'[Dynamic Reward]) = "Reward 1", [Average Reward 1], VALUES('Dynamic Reward'[Dynamic Reward]) = "Reward 2", [Average Reward 2], BLANK())
Average Risk Rating = (SUM(Sheet1[Risk Rating 1]) + SUM(Sheet1[Risk Rating 2]) + SUM(Sheet1[Risk Rating 3]))/ (COUNT(Sheet1[Risk Rating 1]) + COUNT(Sheet1[Risk Rating 2]) + COUNT(Sheet1[Risk Rating 3]))
Average Risk Rating Filter = CALCULATE([Average Risk Rating], FILTER('Dynamic Reward', 'Dynamic Reward'[Reward Selection] > 0))

 

I know the solution should be in the DAX formula for Average Risk Rating Filter, but I can't seem to find the correct DAX formula. 

 

Thank you so much for the help!

 

Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    6 years ago
    Update your Reward Selection to use SELECTEDVALUE which will allow the SWITCH function to actually make it to the BLANK() part:

    Reward Selection = SWITCH( TRUE(), SELECTEDVALUE('Dynamic Reward'[Dynamic Reward]) = "Reward 1", [Average Reward 1], SELECTEDVALUE('Dynamic Reward'[Dynamic Reward]) = "Reward 2", [Average Reward 2], BLANK())

    Then try this for your Average Risk Rating:
    Average Risk Rating = AVERAGEX(FILTER(Sheet1,[Reward Selection]>0 ), (Sheet1[Risk Rating 1]+Sheet1[Risk Rating 2]+Sheet1[Risk Rating 3])/3)

8 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    6 years ago
    Update your Reward Selection to use SELECTEDVALUE which will allow the SWITCH function to actually make it to the BLANK() part:

    Reward Selection = SWITCH( TRUE(), SELECTEDVALUE('Dynamic Reward'[Dynamic Reward]) = "Reward 1", [Average Reward 1], SELECTEDVALUE('Dynamic Reward'[Dynamic Reward]) = "Reward 2", [Average Reward 2], BLANK())

    Then try this for your Average Risk Rating:
    Average Risk Rating = AVERAGEX(FILTER(Sheet1,[Reward Selection]>0 ), (Sheet1[Risk Rating 1]+Sheet1[Risk Rating 2]+Sheet1[Risk Rating 3])/3)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Allison, 

       

      It works! I can now get the average line of 1.67

      For the formula average risk rating, 

       

      Average Risk Rating = AVERAGEX(FILTER(Sheet1,[Reward Selection]>0 ), (Sheet1[Risk Rating 1]+Sheet1[Risk Rating 2]+Sheet1[Risk Rating 3])/3)

       

      Is there anyway to change the "divide by 3" to the count of how many risk rating appeared? So assuming the new data set as follow, for Project 4, the average risk rating will then be 1.5

       

      Project NameRisk Rating 1Risk Rating 2Risk Rating 3Reward 1Reward 2
      Project 111156
      Project 222374
      Project 3333 3
      Project 41 2 2
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I was able to find the solution by changing the formula to below

         

        Average Risk Rating = CALCULATE([Average Risk Rating], FILTER(Sheet1,[Reward Selection]>0 ))
         
        Thank you again for the help!
         
         
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi, 

       

      Thank you for the reply but I am already able to switch measure using the slicer. The problem lies elsewhere.

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    6 years ago
    Maybe try AVERAGEX or a more dynamic formula rather than calculating the average yourself.

    I don't fully understand how you are calculating risk rating, so I don't think this formula gets you there still, but start thinking in terms of the table context, maybe use the FILTER function or similar approach:
    Average Risk Rating = AVERAGEX(Sheet1, Sheet1[Risk Rating 1] + Sheet1[Risk Rating 2] + Sheet1[Risk Rating 3])
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Allison,

       

      Thank you for the reply. The average risk rating is your normal average of the risk rating with the following formula;

      Average Risk Rating = (Risk Rating 1 + Risk Rating 2 +  Risk Rating 3)/3 

       

      I tried using AVERAGEX, but it didn't give me the average line of 1.92 that I wanted.

       

       

      • AllisonKennedy's avatar
        AllisonKennedy
        Community Champion
        6 years ago
        Anonymous I kind of get the Risk Rating 1 +2 + 3 / 3 for total number of Risk Ratings, but since these are in Columns rather than rows, it doesn't play so nicely with DAX, and so when you use AVERAGEX, it will add Risk Rating 1 + 2 + 3, but then what do you want to divide by? And then do you take an average of those averages?

        So for your example it should be:

        AvgRiskRating =
        AVERAGEX(Sheet1,
        (Sheet1[Risk Rating 1] + Sheet1[Risk Rating 2] + Sheet1[Risk Rating 3])
        /3
        )