Average of area by month

[Anonymous]

Hi everyone. I'm trying to calculate the average from differents company's areas by month from the following dataset structure:

So basically, I want to sum all the values of an area on each month and divide by the number of month. How can i do it in a single measure on dax?

You can check the data here: https://docs.google.com/spreadsheets/d/1yIAAD2Sh8fKpDMNip1MHhHPQGBlU7XgL_6QpV1Fb7gs/edit?usp=sharing

It has two workbooks, one with the data (Data) and another with the Average that I want.

[darkinvader_]

Hi @Anonymous ,
You can write a below measure and put it inside value of matrix.

M_Average Value = CALCULATE(SUMX(Table1,DIVIDE(SUM(Table1[Value]),COUNT(Table1[Month-year]))))
In Matrix Put Area in Column and Month-year in Row.
Table1 is your table name

If you find it helpfull please gives a thumbs up.
happy to help you 🙂

Regards
DarkInvader_

[Anonymous]

Hey, @darkinvader_ , thank you so much for ansewering so fast!

Your solution works like a charm for each month. But when i check the total row, it comes with the sum of the months average. I believe there's a way to make this measure with averageX. But I'm failing to do it.

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see the attachment), please check if it is what you want.

Measure = 
VAR _count =
    CALCULATE ( DISTINCTCOUNT ( 'Data'[Month-year] ), ALLSELECTED ( 'Data' ) )
VAR _sumvalue =
    CALCULATE (
        SUM ( 'Data'[Value] ),
        FILTER ( ALLSELECTED ( 'Data' ), 'Data'[Area] = SELECTEDVALUE ( 'Data'[Area] ) )
    )
RETURN
    IF (
        ISINSCOPE ( 'Data'[Month-year] ),
        SUM ( 'Data'[Value] ),
        DIVIDE ( _sumvalue, _count )
    )

Best Regards

[Anonymous]

Thank you so much, @v-yiruan-msft!!