Calculating average over calculated measure

[adva]

Hi

I have a measure in Powerpivot that calculates productivity for 3 different work groups over various weeks (both applied by slicers). The measure includes multiple calculations, a number of filters coded into the measure, as well as the work group and date filters applied by slicers.

I would like to calculate the average productivity for groups 1+2, as well as productivity for groups 1+2+3. I have been looking at making a new measure that could do this for me, so I could still use the date slicer.

However, I'm concerned that I will end up with a very large calculated measure (where I try and combine existing measures with even more built in filters for the work groups).

I'm hoping there is an easier and lighter method to do something as "simple" as averaging different results for the same measure.

Thanks.

 

[marco.russo]

Assuming your measure is named [Productivity], you could write something:

[Productivity_Groups12] := CALCULATE( [Productivity], Group[Product] = "1" || Group[Product] = "2" )
[Productivity_Groups123] := CALCULATE( [Productivity], Group[Product] = "1" || Group[Product] = "2" || Group[Product] = "3" )

This would override the selection in Group[Product] slicer, if you have one.
But I'm not sure I understood the scenario - let me know if you have this or another need.

Marco

 

[adva]

Thanks Marco.

My solution ended up being highly inspires by your input. I did however need to be more low practical, and more of an old fashioned average

[Productivity_Groups12] := (CALCULATE( [Productivity], Group[Product] = "1" )) + (CALCULATE( [Productivity], Group[Product] = "2" )) / 2

 

[marco.russo]

Be careful that the results are not the same - you are calculating an average of two (or more) averages, not a single average.

 

[miguel.escobar]

Thanks Marco.

My solution ended up being highly inspires by your input. I did however need to be more low practical, and more of an old fashioned average

[Productivity_Groups12] := (CALCULATE( [Productivity], Group[Product] = "1" )) + (CALCULATE( [Productivity], Group[Product] = "2" )) / 2

Suggestion: why not divide those by the number of instances? a count would do the job
I agree with Marco on the divided by 2 since sometimes you might not have a product "2" and you'll be dividing just 1 product by 2 (just a suggestion).

 

[adva]

True that it is an average being calculated on already calculated averages - even unweighted (simple) averages.

However, I did end up with the correct results compared with my test scenarios, and was what I was looking for.

But once again - thanks for your answers.