AVERAGE

[Anonymous]

 HOW CAN I GET THE AVERAGE OF THE 3 HIGHLIGHTED ROWS.

[Phil_Seamark]

Hi @Anonymous

A rolling 3 month average or a hardcoded measure to give you the average to those specific days?

[Anonymous]

@Phil_Seamark yes, just the average of those 3 numbers. The numbers are already the way I need them to be, I just need to average those 3. I've tried a AVERAGEX function with it which gets me really close, but for some reason the function changes the numbers slightly for certain months, making the overall average incorrect:

 

The Month End column comes from my date table, my retention and last quarter retention is coming from my fact table. So with the above formula, the only thing I was trying to accomplish was averaging the 3 highlighted values in my [retention] column. So (87.08+87.59+87.46)/3 which should give 87.38. Instead, it is returning (87.03+87.54+87.54)/3 which gives the 87.36. I'm not sure why it is changing my original [retention] values. I know it is a very very small difference, but boss man only wants to accept perfect...

[dedelman_clng]

@Anonymous -

Be aware that mathematically, averaging values that are already ratios themselves is not correct, unless all of the ratio's denominators are exactly the same.  For example

Q1 - 25/100 = .25

Q2 - 20/200 = .10

Q3 - 50/100 = .50

(.25 + .10 + .50) / 3 = .2833

but Average(Q1 thru Q3) = (25+20+50) / (100+200+100) = .2375

So this may be the discrepancy in the two numbers - try recalculating Last Quarter Retention by using the same formula with which you calculate the monthly retention, but sum up the numerators and denominators before dividing.

Hope this helps

David

[Anonymous]

@dedelman_clng I'm not sure how I would do that. Here are the pieces to my retention formula:

So my numerator is [Active]-[Added]. I get what you are saying, I'm just a bit novice in my PBI skills to put together what you've said.

[dedelman_clng]

@Anonymous -

I think you are going to want to essentially recreate the 3 measures used for retention, but with the "last quarter" filter you were using in AVERAGEX.  So create

[LQ Added]

[LQ Active]

[LQ Starting]

and then your [LQ Retention] is essentially the same formula

( [LQ Added] - [LQ Active] ) / [LQ Starting]

[Anonymous]

@dedelman_clng This puts me back to a place I was at previously:

 

It is only returning the last value of the last month of quarter 1. It is not taking february or january into the formula. This is what each individual formula looks like:

[dedelman_clng]

@Anonymous - because you have competing filters in [Active] and [LQ Active], you will likely have to redo the calculation part of [Active] (DISTINCTCOUNT) in [LQ Active] (rather than referring to the other measure).

Also, you may need to "clear" the filters from DATE when calculating the LQ measures (since you're showing the data in table, the measure will inherit the implicit filter from the table

...FILTER(ALL('Date'), 'Date[Year]'=2018...)

Time intelligence is always tricky in DAX/PowerBI - I have found that often you have to resort to building calculations for time comparisons twice.

[Anonymous]

@dedelman_clng I think I see. So my LQ Active would look something like:

LQ Active = CALCULATE(DISTINCTCOUNT(M3[co]),FILTER(M3,M3[startDate]<=MAX('Date'[Date])&&M3[enddate]>=MAX('Date'[Date])),FILTER(ALL('Date'),'Date'[Year]=2018&&'Date'[Quarter]="Qtr 1")))

[dedelman_clng]

Yes - looks like you're on the right track

[Anonymous]

@dedelman_clng I see. I will give it a try and let you know the outcome! Thank you for the advice.