Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Averages for grouped data between a range

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

 

 

 

 

 

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    Perhaps a measure like:

     

    m_MyAverage = ((SUM([AgeRangeLow])+SUM([AgeRangeHigh]/2) * COUNT([Id]))/COUNT([AgeRange])
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        v-ljerr-msft
        Microsoft Employee
        8 years ago

        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. :smileyhappy:

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

         

        Regards