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)
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])
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])
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.
- 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
)