topic Re: Moving Average Calculation Difference in DAX Commands and Tips

Moving Average Calculation Difference

yogeshk77 — Fri, 06 Oct 2023 11:04:20 GMT

I need a help to compare what's causing the difference of output between two identical formulas & how to fix it please.

Refer to this OldPBI, whcih is showing a moving average of "last 6 sprints" (Last column) of story points.

This DAX measure is not showing the result as expected.

I then copied that single table data to another PBI called NewPBI, and there it's working well.

Can someone please do a quick review of the measures & relationships & suggest me what's causing this difference & How do fix teh OldPBI to show correct moving averages?

Re: Moving Average Calculation Difference

Anonymous — Mon, 09 Oct 2023 08:32:16 GMT

Hi yogeshk77 ，

As checked the two pbix files you provided, and in the new file, you get the summary value for the field [Story_Points_10004] directly. In the old file you can also modify the measure [Last 6 Avg] as follows with the similar idea: first get the summary value of each directory, and then get the desired result based on the conditions.

Measure = VAR _SUMMARIZE = SUMMARIZE ( ALLSELECTED ( WeightedVel ), WeightedVel[Learnosity_Team_10202], WeightedVel[Reporting Sprint ID], WeightedVel[Reporting Sprint Name], "SUMOFSP", CALCULATE ( SUM ( WeightedVel[Story_Points_10004] ) ) ) VAR _ADDRANK = ADDCOLUMNS ( _SUMMARIZE, "RANK", RANKX ( FILTER ( _SUMMARIZE, [SUMOFSP] > 0 && [Learnosity_Team_10202] = EARLIER ( [Learnosity_Team_10202] ) ), [Reporting Sprint ID], , DESC, DENSE ) ) VAR _ADDLAST6AVG = ADDCOLUMNS ( _ADDRANK, "LAST6AVG", IF ( [RANK] <= 6, AVERAGEX ( FILTER ( _ADDRANK, [Learnosity_Team_10202] = EARLIER ( [Learnosity_Team_10202] ) && [RANK] >= EARLIER ( [RANK] ) && [RANK] <= EARLIER ( [RANK] ) + 5 ), [SUMOFSP] ) ) ) RETURN SUMX ( FILTER ( _ADDLAST6AVG, [Learnosity_Team_10202] = MAX ( WeightedVel[Learnosity_Team_10202] ) && [Reporting Sprint ID] = MAX ( WeightedVel[Reporting Sprint ID] ) ), [LAST6AVG] )

Best Regards

Re: Moving Average Calculation Difference

yogeshk77 — Tue, 10 Oct 2023 04:38:39 GMT

You are a genius.. Thank you so much !!!