How do I get average values between prescribed percentiles from an unsorted list?

[tk0501]

Hello,
I have a measure that returns 10,000 un-sorted values for each of the 10 years. 

Row - 10,000 samples 

Col - 10 Years

Value - unsorted numbers

How do I display average values between 48 to 52 percentile? 

Data Set As-is

Sample_Num	Year	Value
1	1
1	2
1	3
1	4
1	5
1	6
1	7
1	8
1	9
1	10
2	1
2	2
…	…
10,000	1
10,000	2
10,000	3
10,000	4

Desired Matrix

	Year 1	Year 2	….	Year 10
Average Values between 48-52 percentile
Average Values between 8-12 percentile
Average Values between 88-92 percentile

Thank you!

[v-juanli-msft]

Hi @tk0501 

I make a test as below

But, i'm afraid it doesn't fit your scenario very much.

Please clear me the average should be the average for every year or for every Sample_Num?

Aslo, how to calculate Average percentile?

How to define total average?

Is it like this?

sample_num    year  average_per_year     total_average          percent

1                      1                         2                       5                      2/5

1                      2                         3                       5                      3/5

2                      1                         2                       5                      2/5

2                      2                         3                       5                      3/5

Is my understanding correct?

If so, please refer to my pbix.

If not or if you have any problem, please let me know.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tk0501]

Thank you @v-juanli-msft  Maggie, 

1. I'm defining Percentile by ranking [Sum of all values for each year per sample] 
   • For each Sample - add all the values
   • Rank the samples in order of largest to smallest
   • if you had 100 samples, the top 10 samples would make up [0-10th percentile]
2. Using the above example, for [0-10th percentile] row
   • I want to average of 10 samples worth of values for each year
   • You can repeat this for [50-60th percentile] and [80-90th percentile]

It is difficult for me to show you the numbers because your sample PBI only contains 4 samples. 

If it has 10 or more samples, I can produce my desired outcome in excel 

Does this make it any more sense?

[v-juanli-msft · Accepted solution]

Hi @tk0501 

I use "RAND" function to create a value column which its value will change every time.

So maybe when you look into my pbix, the data is not like the visuals below.

It's just for test.

In my table, i create measures/columns according to your step:

---

@tk0501 wrote:

1. I'm defining Percentile by ranking [Sum of all values for each year per sample] 
   • For each Sample - add all the values  (measure [Sum/sample])
   • Rank the samples in order of largest to smallest  (measure [rank sample])
   • if you had 100 samples, the top 10 samples would make up [0-10th percentile] (column [percentile row])
2. Using the above example, for [0-10th percentile] row
   • I want to average of 10 samples worth of values for each year (measure [average])

---

measure:
Sum/sample = CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table','Table'[sample]) )

rank sample = RANKX(ALL('Table'),[Sum/sample],,DESC,Dense)

column:
percentile row =
SWITCH (
    TRUE (),
    [rank sample] <= 10, "0-10th percentile",
    [rank sample] <= 20, "11-20th percentile",
    [rank sample] <= 40, "21-40th percentile"
)

measure:
average = CALCULATE(AVERAGE('Table'[value]),ALLEXCEPT('Table','Table'[year],'Table'[percentile row]))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.