Scatter plot average line

[Anonymous]

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!

[AllisonKennedy · Accepted solution]

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]

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

[Anonymous]

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!

[AllisonKennedy]

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]

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]

@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
)

[amitchandak]

@Anonymous , In case you want switch measure using slicer , refer this.

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115

[Anonymous]

Hi, 

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