Forum Discussion

tshapiro18's avatar
tshapiro18
New Member
2 years ago

Standard Rate PowerBI

Hi

I have data that represents health care of a population based on age gender and socio-economic class. In my data there are columns for each of the mentioned categories as well as a numerator and denominator. for example: (column name:column content)

measure: 10.2

gender:1

age group: 4

socio economic: 3

numerator:10

denominator:40

 

This would mean that the rate of males, in age group 4, socioeconomic class 3 for measure 10.2 (lets say, rate of flu shots given) is 10/40 (25%).

 

I am trying to standardize this rate to age groups of this measure. Since the population (those found in the denominator) is not equal throughout each age group. The 25% might not be statistically significant. How I would do this manually/in excel - I would created a weighted percentage based on the denominator of each measure by dividing the sum of the denominator per measure by the total denominator of that measure and then using that weight and multiplying it by that 25% mentioned above.

 

How can powerBI do this?

Thanks!

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi tshapiro18 ,

    To solve your problem, here are my test data, I think you can follow these steps:

    1.You can create measure by using DAX:

     

    weighted percentage = 
    var _pre = CALCULATE(
     MAX('Table'[Numerator]),
     FILTER('Table','Table'[Measure] = 10.2 && 'Table'[Gender] = 1 && 'Table'[Age group]=4 && 'Table'[Social economic]=3)
     )
     var _last = CALCULATE(
     MAX('Table'[Denumerator]),
     FILTER('Table','Table'[Measure] = 10.2 && 'Table'[Gender] = 1 && 'Table'[Age group]=4 && 'Table'[Social economic]=3)
     )
     VAR percentage = _pre / _last
     VAR weightedpercentage = DIVIDE(_last,CALCULATE(
        SUM('Table'[Denumerator]),
        FILTER('Table','Table'[Age group]=4 )
     ))
     RETURN
     weightedpercentage

     

    2.final output

    I hope the method as above helps, if not, please provide more details of the problem.

    In order for you to solve the problem faster, you can refer to the following documentation

    How to Get Your Question Answered Quickly - Microsoft Fabric Community

     

    Best Regards,

    Albert He 

  • tshapiro18's avatar
    tshapiro18
    New Member
    2 years ago

    Thank you so much!

    I see the DAX calculation is specific to the measure 10.2 

    I have a bunch of measures and wanted to know if we could make it specific to the measure that I've filtered. Or to each specific measure somehow.

    Thank you again!