cancel
Showing results for
Did you mean: 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  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 =
MaxScores,
"@Perc",
VAR MaxScore = [@Max]
RETURN
DIVIDE (
CALCULATE ( COUNTROWS ( Testing ), Testing[Waarde] < MaxScore ),
CALCULATE ( COUNTROWS ( Testing ) ),
0
)
)
RETURN
AVERAGEX ( Percentiles, [@Perc] )`````` Frequent Visitor

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.  Super User

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. Announcements #### The Power BI Community Show

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

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

Join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison! Top Solution Authors
Top Kudoed Authors
Users online (2,012)