Solved: Re: Finding lowest 10% of total.

Anonymous · ‎08-20-2020

Hi community,

I have a table as below.

Name	Audit Date	Total Score
A	1 - Jun - 2020	45
B	1 - Jul - 2020	47
C	1 - Aug - 2020	48

I followed this blog. I tried to create a measure as follows:

NameRankedTop = RANKX (TABLE_NAME, TABLE_NAME[COLUMN_NAME] ) as well as

NameRankedTop = RANKX (TABLE_NAME, TABLE_NAME[COLUMN_NAME], , ASC )

Both are giving erros.

Please help. Thanks.

harshnathani · ‎08-21-2020

Hi @Anonymous ,

Create a measure

Measure 2 = 
var a = CALCULATE(PERCENTILE.INC('Table'[Total_Score],.10),ALL('Table'))
RETURN
If(MAX('Table'[Total_Score]) < a, 1,0)

Here a is the 10th percentile of you Scores Column i.e 38 based on your data set

Any one who falls below this score is below the 10th percentile.

You can then add a visual filter to show only the bottom 10 percentile scores

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

View solution in original post

v-lionel-msft · ‎08-20-2020

Hi @Anonymous ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

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

Anonymous · ‎08-20-2020

Thank you everyone for suggestions. @v-lionel-msft @harshnathani @Tahreem24 @Anonymous

The formula “__Ranked Bottom = RANKX(Sheet1, [Total Score], , ASC)” is showing no error for a calculated column, but the ranking is not clear. It is showing 4662 for all “50 scores”.

There is slight complication to the data. Multiple persons are having two audits on a given day. Sometimes the scores might be same for two audits for a person, even all the persons across all audit charts.

Here, I want to see all the people who are coming in the bottom 2% score range and later filter it by year/month slicer, which I currently have in the dashboard.

A	01/06/20 0:00	45	1
A	01/06/20 0:00	46	2
B	01/06/20 0:00	45	1
B	01/06/20 0:00	45	2

harshnathani · ‎08-20-2020

HI @Anonymous ,

Can you share some sample data in text format.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Anonymous · ‎08-20-2020

@harshnathani . I hope this would be helpful.

Index	Name	Auditor	Audit_Date	Total_Score
7257	M, P	RA, AB	8/5/2020 0:00	42.5
7324	M, P	RA, AB	8/5/2020 0:00	42.5
7290	A, S	RA, AB	8/4/2020 0:00	40
7351	A, S	RA, AB	8/4/2020 0:00	40
7180	S, S	RA, AB	7/15/2020 0:00	42.5
7055	S, D	TI, MA	7/9/2020 0:00	40
7071	M, H	TI, MA	7/7/2020 0:00	42.5
6920	SA, SH	RA, AB	6/10/2020 0:00	27.5
6460	SR, AN	RA, AB	5/12/2020 0:00	42.5
6521	S, S	TI, MA	5/10/2020 0:00	42.5
6561	SA, BO	TI, MA	5/5/2020 0:00	42.5
6564	SU, SH	TI, MA	5/5/2020 0:00	42.5
6596	SH, MO	TI, MA	4/28/2020 0:00	42.5
6599	M, H	TI, MA	4/22/2020 0:00	42.5
6333	AG, T	TI, MA	4/12/2020 0:00	42.5
6683	AG, T	TI, MA	4/12/2020 0:00	42.5
6347	AQ, AR	TI, MA	4/9/2020 0:00	42.5
6697	AQ, AR	TI, MA	4/9/2020 0:00	42.5
6329	SI, MA	RA, AM	4/9/2020 0:00	42.5
6679	SI, MA	RA, AM	4/9/2020 0:00	42.5
6356	SA, SH	RA, AB	4/8/2020 0:00	42.5
6706	SA, SH	RA, AB	4/8/2020 0:00	42.5
6383	AR, AN	TI, MA	4/5/2020 0:00	42.5
6733	AR, AN	TI, MA	4/5/2020 0:00	42.5
6421	SU, SH	TI, MA	4/1/2020 0:00	42.5
6771	SU, SH	TI, MA	4/1/2020 0:00	42.5
6185	S, S	TI, MA	3/16/2020 0:00	37.5
6266	M, RK	RA, AB	3/5/2020 0:00	42.5
6281	KM, N	TI, MA	3/2/2020 0:00	42.5
6105	AB, AY	TI, MA	1/31/2020 0:00	40
6104	AB, AY	TI, MA	1/30/2020 0:00	40
6097	PE, EL	RA, AB	1/27/2020 0:00	40
6096	PE, EL	RA, AB	1/27/2020 0:00	35
6098	AS, AK	RA, AB	1/26/2020 0:00	37.5
5952	OP, P	IS, SY	1/22/2020 0:00	42.5
5953	PA, KE	IS, SY	1/22/2020 0:00	42.5
5995	JO, SH	RA, AB	1/21/2020 0:00	40
6045	DE, VH	TI, MA	1/19/2020 0:00	40
6050	AQ, AR	TI, MA	1/15/2020 0:00	42.5
6053	S, D	TI, MA	1/15/2020 0:00	40
5988	TH, LI	IS, SY	1/14/2020 0:00	42.5
5992	PA, KE	IS, SY	1/13/2020 0:00	35
5892	RA, KA	IS, SY	1/8/2020 0:00	42.5
5909	VN, A	RA, AB	1/8/2020 0:00	37.5
5917	JO, SH	RA, AB	1/7/2020 0:00	40
5792	TH, AR	IS, SY	1/2/2020 0:00	35
5808	C, TE	RA, AB	1/1/2020 0:00	42.5

harshnathani · ‎08-21-2020

Hi @Anonymous ,

Can you also help me with the output of this data set too.

and if you can explain the output too.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Anonymous · ‎08-21-2020

@harshnathani

Here, I want the score to be sorted in the lowest to highest order, then take 2% of the total rows from the lowest and show the related names in a table. I want to later filter it by year/month slicer, which I currently have in the dashboard.

harshnathani · ‎08-21-2020

Hi @Anonymous ,

Create a measure

Measure 2 = 
var a = CALCULATE(PERCENTILE.INC('Table'[Total_Score],.10),ALL('Table'))
RETURN
If(MAX('Table'[Total_Score]) < a, 1,0)

Here a is the 10th percentile of you Scores Column i.e 38 based on your data set

Any one who falls below this score is below the 10th percentile.

You can then add a visual filter to show only the bottom 10 percentile scores

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Anonymous · ‎08-21-2020

Hi @harshnathani , I tried the above formula. The Measure 2 is giving me "1" for all rows while it is giving the 10th percentile score value for you. Secondly, I see some of your total score show more than 50 whereas 50 is maximum score a person can get. The variable is giving me the correct percentile though.

I want to show only the names that are below 10th percentile.

harshnathani · ‎08-21-2020

HI @Anonymous ,

You have used the PERCENTILEX.INC formula.

Try with PERCENTILE.INC

https://dax.guide/percentilex-inc/

https://dax.guide/percentile-exc/

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

harshnathani · ‎08-21-2020

HI @Anonymous ,

It aggregated the values of Index and Score.

Make sure all are Not summarized.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Tahreem24 · ‎08-20-2020

Try some aggression in your formula and use Calculated column:

NameRankedTop = RANKX (ALL(TABLE_NAME),SUM(TABLE_NAME[COLUMN_NAME] ))

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

Anonymous · ‎08-20-2020

HI @Anonymous

you need an aggreegation on the value.

Consider going thropugh the below link and use as appropriate.

https://docs.microsoft.com/en-us/dax/rankx-function-dax

Tahreem24 · ‎08-20-2020

What error are you getting? Are you using a measure or calculate column?

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

Anonymous · ‎08-20-2020

The error message is as follows:

"A single value for column 'COLUMN_NAME' in the the 'TABLE_NAME' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

@Tahreem24