Quartile calculation based on filters applied on page

[Sri_phani]

Hello, I have a dataset that has Agents and their Metric scores for which I have to calculate Quartile. Q1 is the top quartile and Q4 is the bottom Quartile. 

Sample : 

But when I use the below function, it doesn't give the accurate Quartile. I am assuming it's because it's calculating the Quartile based on entire data set. I have multiple filters like Months, Dates, Department etc. So basically, my table should calculate the Quartile based on agents visible on table by applying all filters applicable on the visual. 

=
VAR AgentScore = [Customer Resolve %]
VAR FilteredData = ALL(Agent_mapping[Agent ID])
RETURN
IF(
          AgentScore <= PERCENTILEX.INC(FilteredData, [Customer Resolve %], 0.25), "Q1",
IF(
          AgentScore <= PERCENTILEX.INC(FilteredData, [Customer Resolve %], 0.50), "Q2",
IF(
           AgentScore <= PERCENTILEX.INC(FilteredData, [Customer Resolve %], 0.75), "Q3", "Q4"
)

)
)

I checked the forum for help, but couldn't find the help I need. Kindly help me with this. 

[ThxAlot]

[tharunkumarRTK]

ALL function will not consider the filters within the visual and also the filters from other visuals. Based on what I understand, you should use ALLSELECTED instead of ALL. If you need any help further then I would request you to share the pbix with sample data and expected output.

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[Sri_phani]

Hi @tharunkumarRTK thank you for responding. I already tried ALLSELECTED. But it is not working with multiple slicers. I am attaching the sample PBI file. It only has two slicers. just to let you know, my data has Team leader, Department, Month as slicers. 

What I am trying to create is, the Quartile should be calculated based on the agent scores available on Visuals based on the slicers we select. 

Sample PBIX file

[tharunkumarRTK · Accepted solution]

@Sri_phani 
I think there is no issue with the filters, If my understanding about your requirement is right then your formula is incorrect

Quartile = 

VAR AgentScore = [Customer Resolve %]
VAR FilteredData = ALLSELECTED(data[Agent ID])

return SWITCH(
    TRUE(),
    AgentScore >= PERCENTILEX.INC(FilteredData, [Customer Resolve %], 0.75), "Q1", 
    AgentScore >= PERCENTILEX.INC(FilteredData, [Customer Resolve %], 0.25), "Q2", 
    AgentScore >= PERCENTILEX.INC(FilteredData, [Customer Resolve %], 0.25), "Q3", 
    "Q4"
) 

Please find the pbix file:here

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun