cancel
Showing results 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

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.

Problem pic

1 ACCEPTED SOLUTION
Community Support

Hi,

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

Best Regards,

Giotto

7 REPLIES 7
Community Support

Hi,

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

Best Regards,

Giotto

Helper III

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

Super User

@vijayvizzu m Are you using Monthyr from Calendar table?

Helper III

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

Super User

Try Avg USD like this

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

Seem like row context issue

Helper III

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

Super User

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors