cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jjkmd Regular Visitor
Regular Visitor

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

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

 

Capture.JPG

 

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.

9 REPLIES 9
Community Support Team
Community Support Team

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

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 Regular Visitor
Regular Visitor

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

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.

 

jjkmd Regular Visitor
Regular Visitor

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

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]))
Super User I
Super User I

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

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
 
 
Untitled.jpg
 
as you can see the new colunm returns the values you wanted




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




jjkmd Regular Visitor
Regular Visitor

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

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.

Capture.JPG

jjkmd Regular Visitor
Regular Visitor

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

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

1

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.

 

Super User IV
Super User IV

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

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
jjkmd Regular Visitor
Regular Visitor

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

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.

 

 

Super User IV
Super User IV

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

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors