Forum Discussion

Samuel43's avatar
Samuel43
Frequent Visitor
2 years ago

PERCENTILEX.INC doesn't return correct value

Hi Community:

 

Background:

 

My matrix displies by row "year month" and column "category" in which there is value to show even at day level.
 
I restored filter to only disply month result at day level becasue I want to use it to compare with every day value afterwards.

 

Here is my DAX: 

_percentile75 =
CALCULATE(PERCENTILEX.INC('Volume_Facility',[Mvalue],0.75),
ALL(),
SUMMARIZECOLUMNS('22-29CalendarTB'[Year Month],Volume_Facility[category]))
 
I also tried context transition just in case:
 
_percentile75 =
SUMX(SUMMARIZECOLUMNS('22-29CalendarTB'[Year Month],Volume_Facility[category]),
CALCULATE(PERCENTILEX.INC(Volume_Facility,[Mvalue],0.75),
ALL(),
SUMMARIZECOLUMNS('22-29CalendarTB'[Year Month],Volume_Facility[category])))
 
Problem:
 
No matter which dax it returns the same value, I could somehow find it's correct in past calculation for example in Apr but it just cannot correctly calculate this month.
 
Raw data for Apri and its result over 30 days

 

But it just cannot calculate data in May correctly for below raw data over 18 days

 

Can you anyone help me? 

 

Thanks!

 


 

Need Help

2 Replies

  • hackcrr's avatar
    hackcrr
    Memorable Member
    2 years ago

    Hi, Samuel43 

    First of all, you probably don't need to use SUMMARIZECOLUMNS inside the CALCULATE function because PERCENTILEX.INC will already compute the quantile inside the supplied column combinations.SUMMARIZECOLUMNS is usually used to generate a dummy table, but in this case, we just need the filter row context.
    Here is a simplified DAX expression that should help you calculate the 75th percentile by Month, Year and Category:

    _percentile75 =  
CALCULATE(  
    PERCENTILEX.INC(Volume_Facility, [Mvalue], 0.75),  
    ALLEXCEPT('22-29CalendarTB', '22-29CalendarTB'[Year Month]),  
    VALUES(Volume_Facility[category])  
)

    If the Volume_Facility table and the '22-29CalendarTB' table are connected by some relationship, and the '22-29CalendarTB' [Year Month] field is actually retrieved from the Volume_Facility table or a related table, then you may not need to ALLEXCEPT function to ALLEXCEPT function, then you may not need to specify it explicitly.

     

    Best Regards,

    hackcrr

    If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly

    • Samuel43's avatar
      Samuel43
      Frequent Visitor
      2 years ago

      Hi hackcrr 

       

      Thanks for your suggestion and I tried your dax but it ended up with a same result.

      No matter your code or my code which isn't as efficient as yours but they all return the same result, meaning it correctly returns values before May but only not for May.

      It should return a value about 426 but instead it returns 393.50 in May.

       

      Kind regards,

      Samuel