Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Problem with using PERCENTILEX.INC

Hello Everyone, 

 

My formula is returning only Q4, or last quarter. I used the formula posted on another forum here, and while I get no errors when inputing in my model, the output is only Q4.

 

Based on the below formula, is the percentilex actually taking where do they fall based on my measure [Customer Invoice Amount] or what am I doing wrong? 

 

For example, I want to categorize my Item number which is stored in table 'Item' according to the measure [Customer Invoice Amount], the lowest quartile, I want it to show Q1, etc 

 

PercentileCategory20 =
VAR LowerMiddle = PERCENTILEX.INC(ALL('Item'[Item Number]), [Customer Invoice Amount], .25)
VAR Middle = PERCENTILEX.INC(ALL('Item'[Item Number]), [Customer Invoice Amount], .50)
VAR UpperMiddle = PERCENTILEX.INC(ALL('Item'[Item Number]), [Customer Invoice Amount], .75)
RETURN
IF([Customer Invoice Amount] < LowerMiddle , "Q1",
IF([Customer Invoice Amount] < Middle , "Q2",
IF([Customer Invoice Amount] < UpperMiddle , "Q3",
IF([Customer Invoice Amount] >= UpperMiddle , "Q4",
"NA"))))
 
Thank you in advance for your help, 
Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    Here I create a easy sample to have a test.

    This measure works in myside.

    As far as I know, you issue should be caused by data model. You can compare mine with yours and check the reason. If this still couldn't help you solve your issue, please share a sample file with me and show me a screenshot with the result you want.

     

    Best Regards,
    Rico Zhou

     

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