cancel
Showing results for
Search instead for
Did you mean:
Highlighted
nidhishah 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/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

1 ACCEPTED SOLUTION

Accepted Solutions 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. 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

## 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. 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!