Obtain Percent of an average based on a subset after adjusting the value used for that subset

[jjkmd]

I'm trying to do the following

Obtain an average based on a subset of members

evaluate if any of those members are greater than 110% of that average

If so, then adjust their score to be exactly 110% of that average - essentiallly capping their effect on a an average

Then recalculate a new average with some members being 'capped' at 110%

then recompare each member to that adjusted average.

Here's some example data

Looking at January,

First calculate average of the 3 partners.  This is 150 (200,100,150)

JJK is greater than 110% of this average of 150.

SO, JJK gets adjusted to 165 (150 x 110%)

THen a new average is calculated from the 3 partners, but using JJK's new 165.

This adjusted average is 138.33 (165,100,150)

Now everone is compared to this new ajusted averages such that output looks like

JJK:  119%.  BJK: 72%   PSP: 108%  TMK: 54%

In excel, I would use " If ( value > 1.1*average, average*1.1, value) " for each subtotal.  Then recalculate an new average based on this row, then calculate everyones percentage based this new average.

I'm fairly new to all this PowerBI, trying to learn DAX, but this one feels like it's outside my reach to learn on my own by trial/error.

[Ashish_Mathur]

Hi,

How did you calculate these figures - JJK:  119%.  BJK: 72%   PSP: 108%  TMK: 54%

[jjkmd]

First we take the average of only the partner group

then we compare each partner to that raw average

if any partner is greater than 110% of that initial average, then they are adusted to be that 110%

Then the average is recalculated a second time with some of those partners being capped at 110

Then the final measure is each person is compared to the new/2nd adjusted average.

[Ashish_Mathur]

Hi,

That still does not answer my question.  Perhaps i have not understood your requirement.  May be someone else will help.

[v-juanli-msft]

Hi @jjkmd 

I don't understand why you calculate average of the 3 partners not 4.

Is the average for the 3 partners calculated for many times?

How about others?

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jjkmd]

In my example table, there are only 3 partners.  The 4th is an associate.

The intent is to calculate the (capped) average of all the partners, and compare each associate to that (capped) Average.

Currently i'm trying to manually calculate an average with the following.

Denominator = a count of partners using.

#parters = CALCULATE(DISTINCTCOUNT(Provider[1Provider]),Provider[Partner] = "Partner")

Numerator = A sum of all the sales.  

#average = calculate(sumx(Monthly,[Charges90%]),Provider[Partner]="Partner")

This seems promising so far.  Going to change those to variables, and try building a calculation field from that.

[AnthonyTilley]

i would do this step by step as you detailed in your explination below is a meausre that will return what your after 

AdjustedAVG% =

-- first get the avarage for each status in a given month

-- Value defined in s is used to filter for calucalting first avarage

var s = "Partner"

var m = MAX(Table1[Month])

var avgsales = CALCULATE(AVERAGEX(Table1,Table1[Sales]),all(Table1),Table1[Month]=m,Table1[Status]=s)

-- next set the upper limit

var ul = avgsales*1.1

--next find any that are bigger than the value

var check1 = if(sum(Table1[Sales])>ul,ul,sum(Table1[Sales]))

--get new total value

var avgsales2 = CALCULATE(sumx(Table1,if(Table1[Sales]>ul,ul,Table1[Sales])),all(Table1),Table1[Month]=m,Table1[Status]=s)

--get the count for avg

var c = CALCULATE(COUNT(Table1[Name]),all(Table1),Table1[Month]=m,Table1[Status]=s)

-- get new avarage

var avg2 = DIVIDE(avgsales2,c)

-- divide the new total check1 by the new avg2

var finalvalue = DIVIDE(check1,avg2)

return finalvalue

as you can see the new colunm returns the values you wanted

[jjkmd]

Thank you.  I'm getting close and learning a lot from looking at how you tackled this.

I think there's an issue because I did not represent the example data well enough - there is actually multiple lines in a given month for each partner/associate for Departnent and Location of Service.  My main data table is called "Monthly".  What I represented as "Sales" in my example data is actually a measure based on summing together these various lines after applying a weighting scale - thats called "Charges90%".  (If it's helpful I can make this a calculated column instead of a measure.).  As a result, I think what i'm getting is an output that is the average of each of these individual lines in a given month for all partners at each location/POS, instead of first summing up all the locations then averaging.

Also, there is a seperate table for Status called "Provider", which is linked to the provider name in the "monthly".  I didn't get the feel when building based of your example that this mattered, but I mentioned this for sake of completeness - there were however a couple times when "Sum" had to be changed to "SumX" to use a measure.

Finally, i'm looking to use a date range filter for the page that is for running 12 months.  I care about the capped average for the whole 12 months, not individual months.

I'm looking forward to seeing what changes enable the above - hopefully that will help some of these things click.

Here is the translation of your method to my actual table list

AdjustedAVG% =

var s = "Partner"

var m = MAX(Monthly[Post_month])

var avgsales = CALCULATE(AVERAGEX(Monthly,Monthly[Charges90%]),all(Monthly),Monthly[Post_month]=m,Provider[Partner]=s)

var ul = avgsales*1.1

var check1 = if(sumx(Monthly,Monthly[Charges90%])>ul,ul,SUMX(Monthly,Monthly[Charges90%]))

var avgsales2 = CALCULATE(sumx(Monthly,if(Monthly[Charges90%]>ul,ul,Monthly[Charges90%])),all(Monthly))

var c = CALCULATE(COUNT(Monthly[Provider]),all(Monthly),Monthly[Post_month]=m,Provider[Partner]=s)

var avg2 = DIVIDE(avgsales2,c)

var finalvalue = DIVIDE(check1,avg2)

Here's what my output looks like.  I made the variable you used Avesales into a measure so I could see what it looks like.

[jjkmd]

Struggling with this all weekend.  Thanks for helping me get started @AnthonyTilley 

Everytime I get close, I hit a new roadblock.  Here is what I have.

Monthly[Charges90Rule] is raw data.   There are multiple lines of this data per month for a given partner.  This is what is messing up any effort of mine to use the Average() function, as I get a very low number.  I need to add the various lines together for a given time period, and THEN take the average.

First did this to get a sum of all the charges for all the partners for given time period.  This works.

Charges All Partners 90% Rule = 
CALCULATE(sum(Monthly[Charges90Rule]),All(Monthly[Provider]),all(Provider),Monthly[Partner Status]="Partner")

Next, I got number of partners in a given month (added a calculation column to data for this), this works.

Number of Partners = 
Calculate(DISTINCTCOUNT(Monthly[Provider]), ALLEXCEPT(Monthly,Monthly[Post_month]),all(Provider),Monthly[Partner Status]="partner")

Next get an average using divide.  This works.

Average Charges per partner 90 = 
DIVIDE([Charges All Partners 90% Rule],[Number of Partners],"")

Next cap those that are over 110% of average for that given time period. - works great.

Charges All Partners 90% Rule Capped = 
sumx(values('Monthly Measures'),[Average charges per partner 90 Capped])

See results here for a several month period.

https://1drv.ms/u/s!AlCaI3WpECWQgbVMpkquIzT9vkZE3Q

(tried embedding an image, system down?)

Here's where I'm stuck.  If I try to add up those items in "Charges All Partners 90% rule capped", it won't work.  The same formula that gave me one number for all lines when performed on a calculated column...

Charges All Partners 90% Rule = 
CALCULATE(sum(Monthly[Charges90Rule]),All(Monthly[Provider]),all(Provider),Monthly[Partner Status]="Partner")

... won't work when tried on the measure...

Charges All Partners 90% Rule Capped = CALCULATE([Average charges per partner 90 Capped],All(Monthly[Provider]),all(Provider),Monthly[Partner Status]="Partner")

What that does is show me the 110% cap value for every row.

Which, interesting enough, is also the (incorrect) sum of the "average charges per partner 90 capped" column.

This has been a worthwhile excercise, learning a lot from it, and really thought I was going to get it on my own.  Now i'm sure I can't without help.  Thanks in advance.

[jjkmd]

Close, not quite

with my denominator, it correctly shows the number 6 for partners *unless* i use individual partners as the x-axis, then it shows a "1" for each partner. 

Tried this with no luck.

#partnersall = CALCULATE(DISTINCTCOUNT(Provider[1Provider]),Provider[Partner] = "Partner",all(Provider[1Provider]))