cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Post Prodigy

## Average of sum by month

Hi there,

I have daily data of some value of last 5 years. I am struggling since morning to create a measure that calculates average in a way that first it calculates the sum of values by month and then calculates the average value.

sum_by_month = calculate(sum[Value],ALLexcept(table,table['month'])

average_value = averagex(ALLEXCEPT(table,table['RY'],sum_by_month)

However, it does not produce the desired output. I have attached the sample data https://1drv.ms/x/s!AucycxZHFe9TjUYX62xq1jm0mV9x?e=ZRyNP1.

Could any one help me where am I making the mistake?

1 ACCEPTED SOLUTION
Super User

Hi @Dunner2020,

It always helps if you show the expected result. It avoids misunderstandings. Try this measure in a card visual:

``````Measure =
VAR sumT_ =
SUMMARIZE ( Table1, Table1[RY], Table1[month] ),
"SumByMonth", CALCULATE ( SUM ( Table1[Value] ) )
)
RETURN
AVERAGEX ( sumT_, [SumByMonth] )``````

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Super User

Hi @Dunner2020,

It always helps if you show the expected result. It avoids misunderstandings. Try this measure in a card visual:

``````Measure =
VAR sumT_ =
SUMMARIZE ( Table1, Table1[RY], Table1[month] ),
"SumByMonth", CALCULATE ( SUM ( Table1[Value] ) )
)
RETURN
AVERAGEX ( sumT_, [SumByMonth] )``````

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Announcements

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

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors