Forum Discussion

yogeshk77's avatar
yogeshk77
Helper I
2 years ago
Solved

Moving Average Calculation Difference

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?

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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