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.
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.
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.
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.
i would do this step by step as you detailed in your explination below is a meausre that will return what your after
Proud to be a Super User!
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.
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.
Hi,
How did you calculate these figures - JJK: 119%. BJK: 72% PSP: 108% TMK: 54%
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.
Hi,
That still does not answer my question. Perhaps i have not understood your requirement. May be someone else will help.
It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.
User | Count |
---|---|
255 | |
65 | |
50 | |
47 | |
46 |
User | Count |
---|---|
362 | |
81 | |
71 | |
66 | |
60 |