Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Percentile

Hi I have a large dataset in which I need to calculate percentile for each sub catogeory has 3 diffrent metric 

If I do it one by one it will make 90 different column.

 

Is there a way to calutake every 10th percentile for each metric by implementing a slicer if I select 10th percentile it shows the data for 10th similar wise 10th to 90th percentile.

 

Here is the command I have wriiten but it dosent seems to be working

 

Percentile For data 1 = VAR

K_Perc = IF(HASONEVALUE(parameter[parameter]),VALUES(parameter[parameter],))
VAR
category = consol[Case CategoryCase TypeCase SubtypeCase Owner SiteCase Language]
RETURN
CALCULATE(PERCENTILE.INC (consol[Case First Touch Cycle Time],K_Perc), FILTER(consol, [Case CategoryCase TypeCase SubtypeCase Owner SiteCase Language] = category))
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Anonymous ;

    Try it.

    Percentile For data 1 =
VAR K_Perc =
    IF ( HASONEVALUE ( parameter[parameter] ), VALUES ( parameter[parameter] ) )
VAR category =
    MAX ( consol[Case CategoryCase TypeCase SubtypeCase Owner SiteCase Language] )
RETURN
    CALCULATE (
        PERCENTILE.INC ( consol[Case First Touch Cycle Time], K_Perc ),
        FILTER (
            ALL ( consol ),
            [Case CategoryCase TypeCase SubtypeCase Owner SiteCase Language] = category
        )
    )

    If this is not correct, please share your simple file and the results you want to output.It makes it easier to give you a solution.

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    I don't immediately see anything wrong with what you wrote (other than the comma inside VALUES). Can you explain what you mean by "doesnt seems to be working"? What results are you getting and what do you expect to get?

     

    It appears you might be trying to define this as a calculated column. Calculated columns cannot read slicer values since they are only calculated once (each time the data model is loaded or refreshed) rather than in response to user interaction. To be able to read an input parameter that the user selects, you'll need to define a measure instead of a calculated column

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I wanted to calculate multiple percentile values by refrencing it to atable which has series of percentile no 0.1 to 0.9

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, Anonymous ;

    Try it.

    Percentile For data 1 =
VAR K_Perc =
    IF ( HASONEVALUE ( parameter[parameter] ), VALUES ( parameter[parameter] ) )
VAR category =
    MAX ( consol[Case CategoryCase TypeCase SubtypeCase Owner SiteCase Language] )
RETURN
    CALCULATE (
        PERCENTILE.INC ( consol[Case First Touch Cycle Time], K_Perc ),
        FILTER (
            ALL ( consol ),
            [Case CategoryCase TypeCase SubtypeCase Owner SiteCase Language] = category
        )
    )

    If this is not correct, please share your simple file and the results you want to output.It makes it easier to give you a solution.

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, Anonymous；

     

    Is your problem solved? If so, Would you mind accept the helpful replies as solutions? Then we could close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

    Best Regards,
    Community Support Team_ Yalan WuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.