Forum Discussion

Saloni_Gupta's avatar
Saloni_Gupta
Icon for Helper I rankHelper I
1 year ago

Calculate Quartile 1 based on user selection

Hi All,

I have a requirement to calculate Percentile (Quartile1) for all the employees in same Line of Business and at same management level (peer) for a metric (Utilization) when filtered on a particular Employee name (consider its LineofBusinessLinkKey when filtered on a particular employee) as shown below:

So, in the above screenshot, If user selects Amar, then calculate Quartile 1 for the utilization values for Consulting:Senior Associate.

this is my dax

Utilization - 25th Quartile =
var selectedemployeeloblinkey = CALCULATE(MAX('Employee Info'[LineofBusinessLinkKey]),'Employee Info'[Full Name] = SELECTEDVALUE('Employee Info'[Full Name]))
VAR FilteredData = ALLEXCEPT('Employee Info','Employee Info'[Employee Id])
var LoBQuartile1 = PERCENTILEX.INC(FilteredData, CALCULATE([Utilization],'Employee Info'[LineofBusinessLinkKey] = selectedemployeeloblinkey),0.25)
RETURN LoBQuartile1

I am not sure what is wrong with this dax but not getting results because of the expression :CALCULATE([Utilization],'Employee Info'[LineofBusinessLinkKey] = selectedemployeesubloslinkey).

Can you please help correct the dax. Thanks in Advance!



Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Saloni_Gupta 

    Please try the following Dax:

    Utilization - 25th Quartile =
VAR SelectedEmployeeName = SELECTEDVALUE('Employee Info'[Full Name])
VAR SelectedEmployeeLOB =
    CALCULATE(
        MAX('Employee Info'[LineofBusinessLinkKey]),
        'Employee Info'[Full Name] = SelectedEmployeeName
    )
VAR Peers =
    FILTER(
        ALL('Employee Info'),
        'Employee Info'[LineofBusinessLinkKey] = SelectedEmployeeLOB
    )
VAR Quartile1 =
    PERCENTILEX.INC(
        Peers,
        [Utilization],
        0.25
    )
RETURN Quartile1

     

     

    If the problem persists, please provide sample data that fully covers your issue (in the form of table or pbix file) and the expected outcome based on the sample data you provided. Please remove any sensitive data in advance.

     

     

     

     

     

    Best Regards,

    Jayleny

     

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