Forum Discussion
Percentile over average value
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.
6 Replies
, Create a measure
- Create a measure that calculates the %3P average for each player in the PLAYERS table:
%3P Average = AVERAGE(PLAYERS[%3P])
- 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
)
- Create another measure that calculates the %3P percentile based on the %3P average: %3P Percentile =
- Create a measure that calculates the %3P average for each player in the PLAYERS table:
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 youHi 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.
Chyba "The PERCENTILE.INC function accepts only a reference to a column as argument number 1."
Neither solution leads successfully to the goal, but the paper can be concluded.
