Lookup Percentil Value in difrent Tables

[SanGraham]

Good Morning everyone,

So i have a Athlete test,
Gender: Male
Age: 17
Position: Stricker
Test : Jump
Result : 37.0

And i have Diferente tables in diferente files with results from:
- Male/Female
- Difrente Age
- Difrent Position
- Difrente Tests
This tables are the scores in percentils from a lot of tests data as you can see in the picture.
So how can i get this athlete test result 37.0 and return in witch percentil he is., based on the gender, age, position and test to choose witch table and column to search the value?

Thanks Veru much.

[AlB · Accepted solution]

@SanGraham 

I'm saying it'd probably be best to transform the tables from this

	Atacante
	Acerelacao	Velocidade	Agilidade
100	Ac1	Vel1	Agi1
95	Ac2	Vel2	Agi2
90	Ac3	Vel3	Agi3

to (intermediate step for illustration purposes)

	Acerelacao	Velocidade	Agilidade	Position
100	Ac1	Vel1	Agi1	Atacante
95	Ac2	Vel2	Agi2	Atacante
90	Ac3	Vel3	Agi3	Atacante

and finally to this:

Position	TypeOfResult	Percentile	Value
Atacante	Acerelacao	100	Ac1
Atacante	Acerelacao	95	Ac2
Atacante	Acerelacao	90	Ac3
Atacante	Velocidade	100	Vel1
Atacante	Velocidade	95	Vel2
Atacante	Velocidade	90	Vel3
Atacante	Agilidade	100	Agi1
Atacante	Agilidade	95	Agi2
Atacante	Agilidade	90	Agi3

Then you can combine all four in a Sheet in one table and add another column with the age (Sub-17 in the example you show). You can then repeat the process for each Sheet and finally combine the resulting table from each Sheet in a final table that has all the info. Whatever operation you want to perform will then be much easier. You can do all these transformations in the query editor, define a function that will process all sheets (assuming they all have the same or very similar structure).

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

[AlB]

Hi @SanGraham 

I think it would be much better to have a single table with all the info. You can combine the ones you have adding columns to specify gender, age, position, etc. You can do that best in the query editor

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

[SanGraham]

@AlB are you sugesting me to put the columns all in on table, maybe rename the columns as for exemple:

MaleU17StrikerJump
So this columns are the scores from 1% to 100% of this grup?

Is that right:

[AlB · Accepted solution]

@SanGraham 

I'm saying it'd probably be best to transform the tables from this

	Atacante
	Acerelacao	Velocidade	Agilidade
100	Ac1	Vel1	Agi1
95	Ac2	Vel2	Agi2
90	Ac3	Vel3	Agi3

to (intermediate step for illustration purposes)

	Acerelacao	Velocidade	Agilidade	Position
100	Ac1	Vel1	Agi1	Atacante
95	Ac2	Vel2	Agi2	Atacante
90	Ac3	Vel3	Agi3	Atacante

and finally to this:

Position	TypeOfResult	Percentile	Value
Atacante	Acerelacao	100	Ac1
Atacante	Acerelacao	95	Ac2
Atacante	Acerelacao	90	Ac3
Atacante	Velocidade	100	Vel1
Atacante	Velocidade	95	Vel2
Atacante	Velocidade	90	Vel3
Atacante	Agilidade	100	Agi1
Atacante	Agilidade	95	Agi2
Atacante	Agilidade	90	Agi3

Then you can combine all four in a Sheet in one table and add another column with the age (Sub-17 in the example you show). You can then repeat the process for each Sheet and finally combine the resulting table from each Sheet in a final table that has all the info. Whatever operation you want to perform will then be much easier. You can do all these transformations in the query editor, define a function that will process all sheets (assuming they all have the same or very similar structure).

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

[SanGraham]

@AlB Thanks Man Very Much,

So now i have my table organized as you showed me,
Now to lookup the TEST Result in my test table, and cross with the score table, but i need multiple conditions: Gender, age, position....
And found the value based on this conditions, for me to return the % Score (100, 95 , 90...)
I need to merge them?
But i can use just one reference to merge, isnt?

[AlB]

Hi @SanGraham 

Can you show me exactly what the result you want would look like? I think it would be best to, now that you have everything in one consolidated table, do the rest in DAX. But I need to understand exactly what you need

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[SanGraham]

Hello  @AlB , many thanks.

So i wil try to explain better what i need.

I need in a personal report from a athlete to return his Percentil based in his test result.
So, he jumped 35cm, based in his caracteristics, 35cm is Percentil 75%. I need to return this percentil.

So now i have two tables as you sugested:
Table 1 - Athlete, Gender, Age, Position, Test Protocol (JUMP), Test Result (35cm)

Table 2 - PercentilScore,   Gender, Age, Position, TestProtocol(JUMP) , Test Results

So i need a lookup value based in multiple condition, am i right?
Based on - Gender, Age, Position, TestProtocol(JUMP) , Test Result (35cm)
Find Return - Percentil Score %

The thing is all the conditions but on are exact maches:
Gender, Age, Position, TestProtocol(JUMP) - Exact Match
Test Result (35cm) - Not Exact Match (if percentil 75% = 33cm , and percentil 80% = 36cm i need to return 80%, as in excel)

Hope i could be more especific this time.
Thanks and sorry my poor english.

[AlB]

Hi @SanGraham 

So you can use FILTER over Table2 and use the exact match for all the other conditions and for the percentile the condition it would be  something like:

VAR currentPercentile_ =  Table2[ScorePercent]

VAR nextPercentile_  = CALCULATE( MIN( Table2[ScorePercent] ), Table2[ScorePercent] > currentPercentile_ )

RETURN

Table1[TestResult] >= currentPercentile_ &&  Table1[TestResult] < nextPercentile_ 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[SanGraham]

@AlB can i use this to have a new column with the Percentil for many diferente atlhletes regarding to diferente tests?

[AlB]

@SanGraham 

Can you copy here a sample of both your tables in text rather than a screen pic? I can then copy them easily and run a quick test.

You can just copy a few of the top rows (including column names) and paste them here

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[SanGraham]

Yes @AlB sure,

Table Scores:

ScorePercent	Gender	Position	AgeCategory	Protocol	ScoreValue
100	Male	Goalkeepers	U20	Sprint	1,66
95	Male	Goalkeepers	U20	Sprint	1,68
90	Male	Goalkeepers	U20	Sprint	1,7
85	Male	Goalkeepers	U20	Sprint	1,71
80	Male	Goalkeepers	U20	Sprint	1,71
75	Male	Goalkeepers	U20	Sprint	1,71
70	Male	Goalkeepers	U20	Sprint	1,73
65	Male	Goalkeepers	U20	Sprint	1,74
60	Male	Goalkeepers	U20	Sprint	1,75
55	Male	Goalkeepers	U20	Sprint	1,75
50	Male	Goalkeepers	U20	Sprint	1,75
45	Male	Goalkeepers	U20	Sprint	1,75
40	Male	Goalkeepers	U20	Sprint	1,75
35	Male	Goalkeepers	U20	Sprint	1,76
30	Male	Goalkeepers	U20	Sprint	1,78
25	Male	Goalkeepers	U20	Sprint	1,81
20	Male	Goalkeepers	U20	Sprint	1,84
15	Male	Goalkeepers	U20	Sprint	1,86
10	Male	Goalkeepers	U20	Sprint	1,89
5	Male	Goalkeepers	U20	Sprint	1,93
100	Male	Goalkeepers	U20	Speed	4,03
95	Male	Goalkeepers	U20	Speed	4,08
90	Male	Goalkeepers	U20	Speed	4,14
85	Male	Goalkeepers	U20	Speed	4,15
80	Male	Goalkeepers	U20	Speed	4,16
75	Male	Goalkeepers	U20	Speed	4,16
70	Male	Goalkeepers	U20	Speed	4,16
65	Male	Goalkeepers	U20	Speed	4,19
60	Male	Goalkeepers	U20	Speed	4,26
55	Male	Goalkeepers	U20	Speed	4,33
50	Male	Goalkeepers	U20	Speed	4,36
45	Male	Goalkeepers	U20	Speed	4,39
40	Male	Goalkeepers	U20	Speed	4,39
35	Male	Goalkeepers	U20	Speed	4,4
30	Male	Goalkeepers	U20	Speed	4,41
25	Male	Goalkeepers	U20	Speed	4,44
20	Male	Goalkeepers	U20	Speed	4,48
15	Male	Goalkeepers	U20	Speed	4,56
10	Male	Goalkeepers	U20	Speed	4,63
5	Male	Goalkeepers	U20	Speed	4,7

Table Tests

Full Name:	Gender:	Test Result	AgeCategory	Position:	Protocol
Sandro Graham Araujo	Male	3,37	U20	Strikers	Speed
Sandro Graham	Male	4,10	U20	Goalkeepers	Sprint
Gustavo Wallace	Male	1,72	U16	Goalkeepers	Sprint

[AlB · Accepted solution]

Hi @SanGraham 

For your calculated column in Table1 (Table Tests), extracting the percentile from Table2 (Tables Scores). You might have to tweak it a bit to get exactly what you need. Note the code checks whether the scorevalues are increasing or decreasing as the percentile increases, so that it can perform the correct operation. 

Extracted_Percentile = 
VAR relevantT_ = FILTER(Table2;Table2[AgeCategory]=Table1[AgeCategory] && Table2[Position] = Table1[Position] && Table2[Gender] = Table1[Gender] && Table2[Protocol]= Table1[Protocol])

VAR valueat100_ = MINX(FILTER(relevantT_;Table2[ScorePercent] = 100);Table2[ScoreValue])
VAR valueat5_ =  MINX(FILTER(relevantT_;Table2[ScorePercent] = 5);Table2[ScoreValue])
VAR orderIsdescending_ = valueat100_ > valueat5_  
VAR percentile_ = 
IF(orderIsdescending_;
    MAXX( FILTER(relevantT_; Table1[Test Result] >= Table2[ScoreValue]);Table2[ScorePercent]);
    MINX( FILTER(relevantT_; Table1[Test Result] >= Table2[ScoreValue]);Table2[ScorePercent]))
RETURN 
percentile_

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[SanGraham]

@AlB Man thanks very much.

I need just to change 2 references, and to change one column type to work.
Thanks very much, you helped me a lot .