cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nidhishah Visitor
Visitor

Measure for Average of Sum

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/2018AApp290
1/1/2018AAqq35
1/1/2018AArr589
2/1/2018AArr987
2/1/2018AAqq149
2/1/2018AApp520
3/1/2018AArr863
3/1/2018AAqq33
3/1/2018AApp195

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Measure for Average of Sum

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.

 

averages.png

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

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




1 REPLY 1
Super User
Super User

Re: Measure for Average of Sum

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.

 

averages.png

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

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!