Constant Average & Cumulative Average

Question

I want to calculate a constant average for weeks from 29 to 41.&nbsp;Test Avg Measure Before Period =&nbsp;AVERAGEX(SUMMARIZE(&nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Date_Dim,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Date_Dim[FISCAL_YEAR_WEEK_NUMBER_KEY] IN {916,917,918,919,920,921,923,924,925,926,927,928}&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; Date_Dim[FISCAL_YEAR_WEEK_NUMBER_KEY],&nbsp; &nbsp; "AVG_VAR",&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE([Total Sales Group M Vs Group N Var],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Date_Dim,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Date_Dim[FISCAL_YEAR_WEEK_NUMBER_KEY] IN {916,917,918,919,920,921,923,924,925,926,927,928}&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ),&nbsp; &nbsp; [AVG_VAR])This works correctly but when added to the table it gives me an incorrect value when added as a card it populates the correct value. I want from Week 29 to Week 41 value as -3283.&nbsp;&nbsp;&nbsp;In addition to this, I want to calculate the cumulative average from Week 42 and so on.&nbsp;Test Avg Measure After Period =VAR Cumulative_Var = SUMX(&nbsp; &nbsp; SUMMARIZE(&nbsp; &nbsp; &nbsp; &nbsp; Date_Dim,&nbsp; &nbsp; &nbsp; &nbsp; Date_Dim[FISCAL_YEAR_WEEK_NUMBER_KEY],&nbsp; &nbsp; &nbsp; &nbsp; "SUM_VAR",&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE([Total Sales Group M Vs Group N Var],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Date_Dim,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Date_Dim[FISCAL_YEAR_WEEK_NUMBER_KEY] &gt; 928 &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Date_Dim[FISCAL_YEAR_WEEK_NUMBER_KEY] &lt;= MAX(Date_Dim[FISCAL_YEAR_WEEK_NUMBER_KEY])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ),&nbsp; &nbsp; [SUM_VAR])VAR Weeks_Count = CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MAX(Date_Dim[FISCAL_YEAR_WEEK_NUMBER_KEY]) - 928,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Date_Dim,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Date_Dim[FISCAL_YEAR_WEEK_NUMBER_KEY] &gt; 928 &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Date_Dim[FISCAL_YEAR_WEEK_NUMBER_KEY] &lt;= MAX(Date_Dim[FISCAL_YEAR_WEEK_NUMBER_KEY])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp;RETURN (Cumulative_Var/Weeks_Count)Week 42 would be 3362, Week 43 would be -3362-2909/2 and Week 44 would be -3362-2909-4746/3 as the number of weeks from 42 to 43 is 2 and from Week 42 to Week 44 is 3.&nbsp;amitchandak&nbsp;Ahmedx&nbsp;Ashish_Mathur&nbsp;parry2k&nbsp;Thanks

kgollapinni · Answer

My Sample Data:FiscalYearWeekTest Avg Measure Before PeriodTest Avg Measure After PeriodTotal Sales Group M Vs Group N Var2023- W29($2,843)&nbsp;($2,843)2023- W30($3,804)&nbsp;($3,804)2023- W31($2,676)&nbsp;($2,676)2023- W32($1,881)&nbsp;($1,881)2023- W33($3,315)&nbsp;($3,315)2023- W34($5,482)&nbsp;($5,482)2023- W36($3,915)&nbsp;($3,915)2023- W37($3,675)&nbsp;($3,675)2023- W38($2,908)&nbsp;($2,908)2023- W39($2,842)&nbsp;($2,842)2023- W40($3,422)&nbsp;($3,422)2023- W41($2,630)&nbsp;($2,630)2023- W42&nbsp;($3,362)($3,362)2023- W43&nbsp;($1,455)($2,909)2023- W44&nbsp;($1,582)($4,746)2023- W45&nbsp;($1,070)($4,280)2023- W46&nbsp;($1,006)($5,028)

ashish_mathur · Answer

Hi,
So in each row of the "Test avg measure before period" you want to see -3283?&nbsp; Am i correct?

kgollapinni · Answer

Hi&nbsp;Ashish_Mathur&nbsp;,Yes, I want to see -3283 for each row of the "Test avg measure before period". I tried using All but if I use all it is going way back till 2019 data. I only want to show for the mentioned weeks -3283 for FY23 W29 to FY23 W41. The table data should start only from FY23 W29. The reason I want this is that I want to add it to a bar chart and show it as W41 which would have -3283 for it and W42 onwards&nbsp;Test Avg&nbsp;Measure&nbsp;After Period logic follows.&nbsp;&nbsp;

ashish_mathur · Answer

Hi,
Would you be OK with a calculated column formula (instead of a measure)?

kgollapinni · Answer

Yes, that would be fine.

Forum Discussion

Constant Average & Cumulative Average

6 Replies

Related content

Cumulative Total

Creating constants

Cumulative Averages by Day and rolled Up

Constant Average

Creating Cumulative Graphs

FiscalYearWeek	Test Avg Measure Before Period	Test Avg Measure After Period	Total Sales Group M Vs Group N Var
2023- W29	($2,843)		($2,843)
2023- W30	($3,804)		($3,804)
2023- W31	($2,676)		($2,676)
2023- W32	($1,881)		($1,881)
2023- W33	($3,315)		($3,315)
2023- W34	($5,482)		($5,482)
2023- W36	($3,915)		($3,915)
2023- W37	($3,675)		($3,675)
2023- W38	($2,908)		($2,908)
2023- W39	($2,842)		($2,842)
2023- W40	($3,422)		($3,422)
2023- W41	($2,630)		($2,630)
2023- W42		($3,362)	($3,362)
2023- W43		($1,455)	($2,909)
2023- W44		($1,582)	($4,746)
2023- W45		($1,070)	($4,280)
2023- W46		($1,006)	($5,028)