Quartiles and Variance

[tmartyn]

Hello,

I have a Power Bi file that calculates quartiles using 'PERCENTILEX.INC' which is working fine. My issue is that I would like to calculate the variance of each quartile and I'm not sure how to go about doing that since I use measures to do a lot of my calculations and it seems variance requires its own column. I don't think I am able to use calculated columns instead of measures because I need the measure to make calculations based on each row.

This is the base code I use to calculate each percentile. There are 2 other measures as well using 0.5 and 0.25 as values.

Percentile P1 =

PERCENTILEX.INC(

    'avgCsat',

    'avgCsat'[Avg CSAT],

    0.75

)

This is the base code to calculate whether an agent belongs in a given quartile based on their score. There are 3 other similar measures for each quartile.

Avg CSAT Q1 =

IF ( DIVIDE (

    CALCULATE (

        COUNT ('Result'[Overall, how satisfied were you with your most recent OLG support experience?]),

        FILTER ('Result', 'Result'[Overall, how satisfied were you with your most recent OLG support experience?] IN { 4, 5 } )

    ),

    CALCULATE ( COUNT ('Result'[Overall, how satisfied were you with your most recent OLG support experience?]))

) >= [Percentile P1],

DIVIDE (

    CALCULATE (

        COUNT ('Result'[Overall, how satisfied were you with your most recent OLG support experience?]),

        FILTER ('Result', 'Result'[Overall, how satisfied were you with your most recent OLG support experience?] IN { 4, 5 } )

    ),

    CALCULATE ( COUNT ('Result'[Overall, how satisfied were you with your most recent OLG support experience?]))

)

)

Scores are calculated using a Top 2 Box scoring method, hence the division using a filter for 4 and 5 (the top 2 boxes). Does anyone have any idea how I can go about calculating the variance of each quartile? 

 This is what one quartile table would look like. I understand the logic, in theory we would want to subtract each score from the average to get the deviation, then square the deviation, sum up all of the squares, and then divide that sum by n-1. I just don't know how I can get access to that 84.21% value or how I can access the Avg CSAT values since they are produced using measures. I am new to Power BI so forgive my lack of understanding. Any ideas or assistance would be greatly appreciated, thanks.

[Anonymous · Accepted solution]

Hi @tmartyn ,

To calculate the variance for each quartile, you can follow these steps:
1. Calculate the Mean for Each Quartile:
You will need to calculate the average (mean) score for each quartile. This can be done by creating a measure that calculates the average for each quartile range.

2. Calculate the Deviation for Each Data Point:
For each data point (score), calculate the deviation from the quartile mean. This is done by subtracting the quartile mean from each score.

3. Square the Deviation:
Square the result of each deviation to get the squared deviation.

4. Sum of Squared Deviations:
Calculate the sum of all squared deviations within the quartile.

5. Calculate the Variance:
Finally, divide the sum of squared deviations by the count of data points in the quartile minus one (n-1) to get the variance.

Quartile Mean = 
AVERAGEX(
    FILTER(
        'YourTable',
        'YourTable'[YourScoreColumn] >= [Percentile P1] &&
        'YourTable'[YourScoreColumn] < [Percentile P2]
    ),
    'YourTable'[YourScoreColumn]
)

Squared Deviation = 
SUMX(
    FILTER(
        'YourTable',
        'YourTable'[YourScoreColumn] >= [Percentile P1] &&
        'YourTable'[YourScoreColumn] < [Percentile P2]
    ),
    POWER('YourTable'[YourScoreColumn] - [Quartile Mean], 2)
)

Variance = 
[Squared Deviation] / 
(COUNTROWS(
    FILTER(
        'YourTable',
        'YourTable'[YourScoreColumn] >= [Percentile P1] &&
        'YourTable'[YourScoreColumn] < [Percentile P2]
    )
) - 1)

Please replace 'YourTable' and 'YourScoreColumn' with the actual names of your table and column. Also, adjust the [Percentile P1] and [Percentile P2] to match the quartile ranges you are using.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.