Forum Discussion
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 for...
- 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)
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)
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)
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 Name Risk Rating 1 Risk Rating 2 Risk Rating 3 Reward 1 Reward 2 Project 1 1 1 1 5 6 Project 2 2 2 3 7 4 Project 3 3 3 3 3 Project 4 1 2 2 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!