Forum Discussion
compare individual data points to average trend
You may try the measure below.
Measure = AVERAGEX ( ALLSELECTED ( 'Table'[WeekId] ), [ChurnCalc] ) - [ChurnCalc]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]
Solution SageWhen 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.
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-msftCommunity Support
You may try the measure below.
Measure = AVERAGEX ( ALLSELECTED ( 'Table'[WeekId] ), [ChurnCalc] ) - [ChurnCalc]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]