Forum Discussion

Sammy22's avatar
Sammy22
Helper I
1 year ago
Solved

Table with conditional quartiles

i have a table with discounts for countries. I want to calculate the quartile 1 of the region for the specific product and then the quartile 1 of all the countries regardless of region based on the product. 

 

CountryRegionProductCountry DiscountQ1 RegionQ1 Overall
ChinaEastern AsiaA0.54  
KoreaEastern AsiaB0.52  
JapanEastern AsiaC0.51  
TaiwanEastern AsiaA0.62  
SloveniaSouthern EuropeB0.69  
HungaryEastern EuropeC0.55  
Czech RepublicEastern EuropeA0.49  
PolandEastern EuropeB0.43  
SlovakiaEastern EuropeC0.53  
RomaniaEastern EuropeA0.7  
BulgariaEastern EuropeB0.66  
  • Sammy22's avatar
    Sammy22
    1 year ago

    Managed to find a solution. This is calculating the 75th percentile:

     

    75th Percentile Discount% = calculate(PERCENTILE.INC('Table'[Country_Discount%] ,0.75),ALLEXCEPT('Table',Table[PRODUCT]))

4 Replies

  • Sammy22's avatar
    Sammy22
    Helper I
    1 year ago

    Managed to find a solution. This is calculating the 75th percentile:

     

    75th Percentile Discount% = calculate(PERCENTILE.INC('Table'[Country_Discount%] ,0.75),ALLEXCEPT('Table',Table[PRODUCT]))
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Thanks lbendlin for the helpful thoughts.

      Hi Sammy22 ,

       

      I'm going to make some additions to yours, here I've created two calculated columns:

      Q1 Region = 
CALCULATE(
    PERCENTILE.INC('Table'[Country Discount], 0.25),
    ALLEXCEPT('Table', 'Table'[Region],'Table'[Product]))

Q1 Overall = 
CALCULATE(
    PERCENTILE.INC('Table'[Country Discount],0.25),
    ALLEXCEPT('Table','Table'[Product]))

      The result is as follows:

       

      Best Regards,
      Zhu
      Community Support Team

       

      If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
      If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

       

  • Sammy22's avatar
    Sammy22
    Helper I
    1 year ago

    i have a table with discounts for countries. I want to calculate the quartile 1 of the region for the specific product and then the quartile 1 of all the countries regardless of region based on the product. 

     

    CountryRegionProductCountry DiscountQ1 RegionQ1 Overall
    ChinaEastern AsiaA0.54  
    KoreaEastern AsiaB0.52  
    JapanEastern AsiaC0.51  
    TaiwanEastern AsiaA0.62  
    SloveniaSouthern EuropeB0.69  
    HungaryEastern EuropeC0.55  
    Czech RepublicEastern EuropeA0.49  
    PolandEastern EuropeB0.43  
    SlovakiaEastern EuropeC0.53  
    RomaniaEastern EuropeA0.7  
    BulgariaEastern EuropeB0.66
  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    You would use PERCENTILEX for that.  Have you tried that?

     

    Note that your data is insufficient for this. You need many more datapoints.