Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vijayvizzu
Helper III
Helper III

Average Cumulative not working

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 picProblem pic

1 ACCEPTED SOLUTION
v-gizhi-msft
Community Support
Community Support

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

View solution in original post

7 REPLIES 7
v-gizhi-msft
Community Support
Community Support

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

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

amitchandak
Super User
Super User

@vijayvizzu m Are you using Monthyr from Calendar table?

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

 

model.JPG

@vijayvizzu 

Try Avg USD like this

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

 

Seem like row context issue

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

p2.JPG

 

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.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.