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 ...
Need Help
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