Average of Variables

[Berten]

Hi,

I am calculating the average of the sum of the percentiles of the max values. So I have a table with testresults (values) from a certain category. What I did is, I calculated the MAX of each variable, then got the percentile of each value in the category, then summed them to get the average, but my method is not really scalable when I have to average more than 3 values.

Is there a better way?

Test Category (2) =

-- define the variables and get the max of them

VAR MaxFirst = CALCULATE(MAX(Testing[Waarde]),Testing[Kenmerk] = "Bench Score")

VAR MaxSecond = CALCULATE(MAX(Testing[Waarde]),Testing[Kenmerk] = "SDS Score")

VAR MaxThird = CALCULATE(MAX(Testing[Waarde]),Testing[Kenmerk] = "Watt Bike")

-- Calculate the percentile of each individual score in the category

-- Calculate the percentile of FIRST variable

VAR PercFirst =

DIVIDE(CALCULATE(

COUNTROWS('Testing (2)'),'Testing (2)'[Waarde] < MaxFirst, 'Testing (2)'[Kenmerk] = "Bench Score",'Testing (2)'[Waarde]>0),

CALCULATE(

countrows('Testing (2)'),'Testing (2)'[Kenmerk] = "Bench Score"),

0)

-- Calculate the percentile of SECOND variable

VAR PercSecond =

DIVIDE(CALCULATE(

COUNTROWS('Testing (2)'),'Testing (2)'[Waarde] < MaxSecond, 'Testing (2)'[Kenmerk] = "SDS Score",'Testing (2)'[Waarde]>0),

CALCULATE(

countrows('Testing (2)'),'Testing (2)'[Kenmerk] = "SDS Score"),

0)

-- Calculate the percentile of THIRD variable

VAR PercThird =

DIVIDE(CALCULATE(

COUNTROWS('Testing (2)'),'Testing (2)'[Waarde] < MaxThird, 'Testing (2)'[Kenmerk] = "Watt Bike",'Testing (2)'[Waarde]>0),

CALCULATE(

countrows('Testing (2)'),'Testing (2)'[Kenmerk] = "Watt Bike"),

0)

-- Divide the variables to get the average

return

IF(PercFirst > 0 && PercSecond > 0 && PercThird > 0, (PercFirst + PercSecond + PercThird)/3,

IF((PercFirst = 0 && PercSecond > 0 && PercThird > 0) || (PercFirst > 0 && PercSecond = 0 && PercThird > 0) || (PercFirst > 0 && PercSecond > 0 && PercThird = 0), (PercFirst + PercSecond + PercThird)/2, (PercFirst + PercSecond + PercThird))

[AlexisOlson]

The trick is to work with tables rather than individual variables.

I can't promise this will work perfectly but it should help point you in the right direction:

Average Percentile =
VAR Categories =
    TREATAS ( { "Bench Score", "SDS Score", "Watt Bike" }, Testing[Kenmerk] )
VAR MaxScores =
    ADDCOLUMNS ( Categories, "@Max", CALCULATE ( MAX ( Testing[Waarde] ) ) )
VAR Percentiles =
    ADDCOLUMNS (
        MaxScores,
        "@Perc",
            VAR MaxScore = [@Max]
            RETURN
                DIVIDE (
                    CALCULATE ( COUNTROWS ( Testing ), Testing[Waarde] < MaxScore ),
                    CALCULATE ( COUNTROWS ( Testing ) ),
                    0
                )
    )
RETURN
    AVERAGEX ( Percentiles, [@Perc] )

[Berten]

Hi Alexis,

Thanks for the reply. Doesn't seem to work.. Probably because I work in two different tables to filter afterwards using the Testing 2 for benchmark purposes. 

[AlexisOlson]

Not surprising. You'll need to adjust it for your exact situation. I intended it more as a suggested pattern than a final solution since I have nothing to test against.