Average Cumulative not working

[vijayvizzu]

Hi Folks, I have been trying to fix the error, unable to resolve it. I had created a measure "Avg USD" which normally calculate average values for each month. Its working good, and now i had creaetd another measure "Cummulative Avg" to create a cumulative of average measure, which doesn't work.
Hence to check the calculation, i had created a measure "Total USD" to calculate the sum of values of a column, in this measure i just replaced the AVERAGEX function with SUMX, its working good too. Then i had created a measure "Cummulative" to generate the cummulative of Sum. This is working fine too.
I was wondering, why the same structure behaves differently with SUMX and AVERAGEX.
Please see the snapshot i have attached.
Could you please help,where i am doing wrong. I recently started working on this Power BI.

Problem pic

[v-gizhi-msft · Accepted solution]

Hi,

Please try this measure:

Cummulative Avg =
SUMX (
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( Calendar ),
            'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
        ),
        'Calendar'[MonthYr],
        "Avg", [Avg USD]
    ),
    [Avg]
)

Best Regards,

Giotto

[vijayvizzu]

@v-gizhi-msft @amitchandak Thank you everyone for your support

[amitchandak]

@vijayvizzu m Are you using Monthyr from Calendar table?

[vijayvizzu]

@amitchandak : Yes, i am using custom calender table. Please see the relational model for your reference.

[amitchandak]

@vijayvizzu 

Try Avg USD like this

AverageX(summarize(CALENDAR,CALENDAR[MonthYr],"_sum",[Total USD]),[_sum])

Seem like row context issue

[vijayvizzu]

@amitchandak I have modified existing formula with your formula, but seems doesn't work. It gives me same values as [Total USD] measure.

[amitchandak]

You want to a sum till moth level and then do Avg. If yes, We need make sure data grouped at month level and avg took after that.

So the change would, you replace [Total USD] with what ever formaul you want

AverageX(summarize(CALENDAR,CALENDAR[MonthYr],"_sum",[Total USD]),[_sum])

If it did not work

Can you share sample data and sample output.