compare individual data points to average trend

[spena]

Hi, I need help with the DAX for what I am trying to do and I want to be able to show this in a graph once calculated.

I am showing annualized churn over time, by week. There is a date slider in my dashboard to select which weeks to view. Based on the weeks selected, the annualized churn for each week will be calculated and shown.

My churn measure formula is
ChurnCalc = CALCULATE((ChurnDigital[StopCopiesTotal]/ChurnDigital[AvgSubCopies]/ChurnDigital[AnnualizationFactor]*52)).

This ChurnCalc is also comprised of the following measures:

• StopCopiesTotal = CALCULATE(SUM(ChurnDigital[StopCopies]),DATESBETWEEN(ChurnDigital[WeekId],[MinDate],[DistinctMaxDate]))
• AvgSubCopies = [SubCopiesTotal]/[AnnualizationFactor]
• AnnualizationFactor = DATEDIFF([MinDate],[DistinctMaxDate],WEEK) +1

DistinctMaxDate is a measure also:

DistinctMaxDate =

MAXX(KEEPFILTERS(VALUES('ChurnDigital'[WeekId])), CALCULATE([MaxDate]))

I have added the average trend line via the analytics tab in the Visualizations pane. What I need is the overall average of all the ChurnCalc column, to compare to each week's ChurnCalc value, and find the difference, but am having trouble calculating it as it is a measure.

Here is a table of my values for the date range selected 2/16/20 - 3/8/20 to give a better idea of how I am calculating things. So the overall ChurnCalc average for this period is 60.10%. The point difference then on Feb 23 would be 4.7. 

Thanks for any help.

WeekId	AnnualizationFactor	SubCopies	StopCopies	ChurnCalc	StopCopiesTotalThruDate	DistinctMaxDate	MinDate
2/16/2020	1	39745	459	60.05%	459	2/16/2020	2/16/2020
2/23/2020	2	40139	392	55.40%	851	2/23/2020	2/16/2020
3/1/2020	3	40522	600	62.66%	1451	3/1/2020	2/16/2020
3/8/2020	4	41201	485	62.29%	1936	3/8/2020	2/16/2020

[v-chuncz-msft · Accepted solution]

@spena 

You may try the measure below.

Measure =
AVERAGEX ( ALLSELECTED ( 'Table'[WeekId] ), [ChurnCalc] ) - [ChurnCalc]

[kentyler]

When you say "What I need is the overall average of all the ChurnCalc column, to compare to each week's ChurnCalc value, "

It makes me think... maybe ALL() to the rescue.

A measure can call a measure, so you could write a measure that called ChurnCalc()

Total ChurnCalc = CALCULATE(SUM(ChurnCalc),ALL(ChurnDigital))

The ALL would remove any row context generated by a visual and give you access to all the data in your table.

I suspect that would be pretty inefficient.

You could think about writing a separate measure to calculate the yearly average. You would have to change the way the date range is defined

,DATESBETWEEN(ChurnDigital[WeekId],[MinDate],[DistinctMaxDate]))

since the average of all the weeks in the year added up should be the same as the average for the whole year.

[spena]

Hi @kentyler Thank you for replying. You are correct about ALL--I don't think ALL would give me what I need as my calculations depend on the WeekIds being called into the visual. Also I did try the formula you provided Total ChurnCalc = CALCULATE(SUM(ChurnCalc),ALL(ChurnDigital)) and it wouldn't let me sum the ChurnCalc measure (only allowed me to choose columns). 

Unfortunately I don't think the second suggestion would give me what I need since I have dates that go back to the beginning of January 2018 and the visual allows the user to view any date range they choose. My bar graph could show a single column or 26 columns depending on my date slicer, and the first week would depend on the slicer as well... so the average trend line value will change as I move the slicer (and not just show average trend for a particular year or so forth, but rather for any given date range). This is why I have had to make measures for AnnualizationFactor, MinDate, DistinctMaxDate, so that the calculations will adjust based on the date ranges provided.

Hopefully that makes sense. Thanks again for the input!

[v-chuncz-msft · Accepted solution]

@spena 

You may try the measure below.

Measure =
AVERAGEX ( ALLSELECTED ( 'Table'[WeekId] ), [ChurnCalc] ) - [ChurnCalc]

[spena · Accepted solution]

Hi @v-chuncz-msft thank you for the suggestion!

I was able to solve this by creating the following measures:

ChurnCalc average =

AVERAGEX(

    ALLSELECTED('ChurnDigital'[WeekId]),

    CALCULATE([Churn])

)

Churn var to avg = ChurnDigital[Churn] - ChurnDigital[ChurnCalc average]