Forum Discussion
How do I get average values between prescribed percentiles from an unsorted list?
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:- 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])
- 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
MaggieCommunity 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.- I'm defining Percentile by ranking [Sum of all values for each year per sample]
Community SupportHi 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.
Thank you v-juanli-msft Maggie,
- 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]
- 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
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:- 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])
- 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
MaggieCommunity 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. - I'm defining Percentile by ranking [Sum of all values for each year per sample]