topic Re: Quartile calculation based on filters applied on page in DAX Commands and Tips

Quartile calculation based on filters applied on page

Sri_phani — Sat, 13 Apr 2024 07:58:34 GMT

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.

Re: Quartile calculation based on filters applied on page

tharunkumarRTK — Sat, 13 Apr 2024 08:01:38 GMT

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

Re: Quartile calculation based on filters applied on page

Sri_phani — Sat, 13 Apr 2024 12:52:15 GMT

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

Re: Quartile calculation based on filters applied on page

tharunkumarRTK — Sat, 13 Apr 2024 13:48:14 GMT

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

Re: Quartile calculation based on filters applied on page

ThxAlot — Sun, 14 Apr 2024 08:30:13 GMT