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 co...
Need Help
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