cancel
Showing results for
Did you mean:
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 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?

1 ACCEPTED SOLUTION
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")
6 REPLIES 6
Helper I

Example PowerBI file:

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.

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

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

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

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.

Responsive Resident

Hello @Mtelf24

Helper I

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

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors