Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
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.
@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
User | Count |
---|---|
57 | |
21 | |
19 | |
16 | |
16 |
User | Count |
---|---|
87 | |
77 | |
52 | |
37 | |
21 |