Percentile over average value

[Petr_Durana]

I have table PLAYERS and in it columns YEAR, LEAGUE, PLAYER and %3P.
I have slickry over YEAR and LEAGUE columns.
I have a visual type table in which the PLAYER and %3P columns are the average values for the respective player from the %3P column.
The players displayed are based on those two slickers.
I need to add a %3P_PERCENTIL column to show the percentile of the %3P AVERAGE, i.e. the percentage of players who have a %3P average less than that player.
I can address the percentile of the %3P values, but not their averages.

[Petr_Durana]

Neither solution leads successfully to the goal, but the paper can be concluded.

[Petr_Durana]

Formula:
%3P Average = AVERAGE(PLAYERS[%3P])
reports an error:
"The %3P column in the PLAYERS table cannot be found or is probably not used in this expression."
The %3P column is a measure:
%3P = DIVIDE(SUM('PLAYERS'[3PM]), SUM('PLAYERS'[3PA]))
How to solve this?
Thank you

[v-yajiewan-msft]

Hi @Petr_Durana , Hello @bhanu_gautam ,

Thank you for your prompt reply!

Based on your description, you want to create a new measure based on another measure value.

Measure1: %3P = DIVIDE(SUM('PLAYERS'[3PM]), SUM('PLAYERS'[3PA]))

Measure2: %3P Average = AVERAGE(PLAYERS[%3P])

Please use the AverageX instead of average function as shown below to have a test:

%3PAverage = AVERAGEX(VALUES(PLAYER),PLAYER[%3P])

Remember to format it as Percentage as shown below:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Petr_Durana]

Chyba "The PERCENTILE.INC function accepts only a reference to a column as argument number 1."

[v-yajiewan-msft]

Hi @Petr_Durana ,

As far as I know, the Percentile.INC function doesn't directly support measure because measure is not a static column.

As a workaround, consider using calculated columns instead of measures under the Percentile.inc function.

Best regards,

Joyce

[bhanu_gautam]

 , Create a measure  

1. Create a measure that calculates the %3P average for each player in the PLAYERS table:
   %3P Average = AVERAGE(PLAYERS[%3P])
   
   

   2. Create another measure that calculates the %3P percentile based on the %3P average: %3P Percentile =
      VAR PlayerAverage = [3P Average]
      RETURN
      PERCENTILE.INC(
      CALCULATETABLE(
      VALUES(PLAYERS[PLAYER]),
      ALLSELECTED(PLAYERS),
      PLAYERS[%3P Average] < PlayerAverage
      ),
      PlayerAverage
      )