Above/Below Average Classification

[Mtelf24]

I have two formulas to calculate the overall average of two bits of data in my dataset.

The first:

AverageRetention =  DIVIDE([Won Count Measure],[Won Count Measure] + [Lost Count Measure])

where Won Count Measure is just a count of rows in my dataset where status = won and lost the same but where status = lost.

That gives me an overall average for retention (if used in a card or summarized in the matrix) and I can see it for each person if I put the measure in alongside the salesperson in a matrix. The source data looks similar to:

Opportunity ID	SalesPerson	Status	Revenue
1	Person A	Won	$3344.54
2	Person B	Lost	$5763.33
3	Person C	Lost	$9009.00
4	Person B	Won	$12454.99
5	Person A	Lost	$865.00
6	Person D	Won	$950.00

I would like to be able to take the measure as applied to each individual person compared to the overall

I know this would require a comparison measure and in the same measure (or seperetely) there would possibly be an IF condition to check if the comparison measure is greater or less than the overall average. What I'd like to get to is:

Salesperson	AverageRetention Measure	New Above/Below Average Measure
Person A	80.00%	Above Average
Person B	54.00%	Below Average
Person C	62.44%	Above Average
Person D	35.33%	Below Average
Person E	60.47%	Below Average
Total	61.53%	(not summarized)

The second should be conceptually the same so I don't know if it requires an answer if the above is, but just in case, for the revenue figure I've just done AverageRevenue = AVERAGE(revenue)

How do I apply the context of the salesperson average retention or revenue against the overall average?

[Mtelf24]

Example PowerBI file:

https://drive.google.com/file/d/1-gASiTeFSy3rE0mNJt2OGUDMYEGq1I9n/view?usp=sharing

I have included the working formula in there for revenue but I still can't work out how to do the same for my retention figure.

[Anonymous]

Hello @Mtelf24 
If the below output is expected then you can try this measure.

AverageRetAboveBelow =

VAR averageRevenue =

CALCULATE(

[Retention Measure],

ALL( Opportunities[SalesPerson] )

)

RETURN

IF (

[Retention Measure] >= averageRevenue,

"Above Average",

"Below Average"

)

If it's not as expected then please share the expected output.

[Mtelf24 · Accepted solution]

OK I worked out the formula I wanted to do for the average comparison for the revenue and it is working how I want it. Now I'm going to see if I can apply the same for the retention %...

AverageRevenueCompare =

VAR averageRevenue =

CALCULATE( AVERAGE (Table[Revenue]),ALL(Table[SalesPerson]))

RETURN

IF (AVERAGE(Table[Revenue]) >= averageRevenue, "Above Average","Below Average")

[Mtelf24]

Applying a similar formula doesn't work for 

AverageRetention =  DIVIDE([Won Count Measure],[Won Count Measure] + [Lost Count Measure])

So I'm still working that out.

But the formula I posted in message 4 works great for the revenue field.

[Anonymous]

Hello @Mtelf24 
Please check this link

[Mtelf24]

Hi @Anonymous That is using summarized data into a different table and a calculated column. My data is not summarized and if I do summarize it I'm concerned I won't then be able to filter it by fields on the original dataset such as date. If I do need to summarize I'd rather do it inside a measure if possible using SUMMARIZE()