cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mtelf24
Helper I
Helper I

Above/Below Average Classification

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 IDSalesPersonStatusRevenue
1

Person A

Won$3344.54
2Person BLost$5763.33
3Person CLost$9009.00
4Person BWon$12454.99
5Person ALost$865.00
6Person DWon$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:
 
SalespersonAverageRetention MeasureNew Above/Below Average Measure
Person A80.00%Above Average
Person B54.00%Below Average
Person C62.44%Above Average
Person D35.33%Below Average
Person E60.47%Below Average
Total61.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?

1 ACCEPTED SOLUTION
Mtelf24
Helper I
Helper I

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

View solution in original post

6 REPLIES 6
Mtelf24
Helper I
Helper I

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.

TarunSharma
Responsive Resident
Responsive Resident

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"
)
TarunSharma_0-1623737738461.png

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



Mtelf24
Helper I
Helper I

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

View solution in original post

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.

TarunSharma
Responsive Resident
Responsive Resident

Hello @Mtelf24 
Please check this link

Hi @TarunSharma 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()

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!