Measure for Average of Sum

[nidhishah]

Hi,

I have values for each category, sub-category by month. What i want to create is a measure (or some other way) to show values by category by Calendar quarter - but the values need to be SUMed up at category level and AVERAGED over calendar months in that quarter. 

month              Cat          subcat    value

1/1/2018	AA	pp	290
1/1/2018	AA	qq	35
1/1/2018	AA	rr	589
2/1/2018	AA	rr	987
2/1/2018	AA	qq	149
2/1/2018	AA	pp	520
3/1/2018	AA	rr	863
3/1/2018	AA	qq	33
3/1/2018	AA	pp	195

Sum of AA in Jan = 914

Sum of AA in feb = 1656

Sum of AA in mar = 1091

I want to show 1220 as Q1 average. But power BI is showing average of all 9 values as 406.7.

Any help?

thanks

Nidhi

[MFelix · Accepted solution]

Hi @nidhishah ,

Not sure if you are using a measure or a simple average from the column but  in DAX the values are calculated based on context so when you add all values to your visual you are getting the average of the lines has one data set in this case you want to have the sum of the averages so you need to create a SUMX measure:

Average Total =
VAR average_values =
    AVERAGE ( Table1[Value] )
RETURN
    IF (
        HASONEFILTER ( Table1[Month] );
        average_values;
        SUMX ( ALLSELECTED ( Table1[Month] ); average_values )
    )

As you can see below on the several visuals you can see that if I add the month to the visual you get 914 and so on otherwise you get the 1220.

Be aware that if you want to add the subcategory or some other columns this measure need to be changed.

Regards,

MFelix