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

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

Reply
Dunner2020
Post Prodigy
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
AlB
Super User
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_ =
    ADDCOLUMNS (
        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 

SU18_powerbi_badge

View solution in original post

1 REPLY 1
AlB
Super User
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_ =
    ADDCOLUMNS (
        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 

SU18_powerbi_badge

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.