cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Berten
Frequent Visitor

Average of Variables

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))



3 REPLIES 3
AlexisOlson
Super User
Super User

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] )

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. 

 

 

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.

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it Out!

Join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!