Averages for grouped data between a range

[Anonymous]

Hello,

We have a data warehouse that connects via direct query. We have dimension tables that contains ranges of data. For example, dates ranges, income ranges, gender.

The following image contain an example Matrix visulaisation, related to an age of a person. We would like to work the overall average age value?  

Selecting, the display value as an average option (the last column), gives an incorrect result.

Using excel I can work out an an average by:

• adding the two range value foreach row together and then dividing by 2 to get a mid value,
• then multiplying this by a freqency values (no. of people) foreach row to get a mid * freqency values,  
• then sum the mid * freqency value and divide this by the sum of the freqency values

How would I appraoch this within power bi desktop. This the an inbuilt option that can work out an average or would we need to use DAX to create a measure or extra column

Thanks

[Greg_Deckler]

Perhaps a measure like:

m_MyAverage = ((SUM([AgeRangeLow])+SUM([AgeRangeHigh]/2) * COUNT([Id]))/COUNT([AgeRange])

[Anonymous]

Hi smoupre,

Your measure was useful. But, I wasn't able to get this to work.

I tried the following measure:

Mid = 
VAR     
    AgeRangeMidPoint = DIVIDE(
        (SUM('dim AgeRanges'[Minimum]) + SUM('dim AgeRanges'[Maximum])),2
    )
RETURN
    (AgeRangeMidPoint * COUNT('fact Calculations'[Id]))

Then used it as the last column of the following matrix visualisation:

What I would need to do is to:

• Get the Sum of the measure value Mid (note that overal total in the attached image is incorrect),
• Get the Sum of Id,
• Then divide by the Sum of the measure values by the Sum of the Id total

Thanks

[v-ljerr-msft]

Hi @Anonymous,

According to your description above, you should be able to use the formula below to get the Sum of the measure value Mid. 

TotalMid =
SUMX (
    SUMMARIZE ( 'dim AgeRanges', 'dim AgeRanges'[AgeRange], "abc", [Mid] ),
    [abc]
)

Regards