Average of sum by month

[Dunner2020]

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?

[AlB · Accepted solution]

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