Forum Discussion

tk0501's avatar
tk0501
Frequent Visitor
7 years ago
Solved

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

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!

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      tk0501
      Frequent Visitor
      7 years ago

      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's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        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.