Forum Discussion

Sri_phani's avatar
Sri_phani
Icon for Helper III rankHelper III
2 years ago
Solved

Quartile calculation based on filters applied on page

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. 

DAX
  • tharunkumarRTK's avatar
    tharunkumarRTK
    2 years ago

    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

     

4 Replies

  • tharunkumarRTK's avatar
    tharunkumarRTK
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Sri_phani
      Icon for Helper III rankHelper III
      2 years ago

      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's avatar
        tharunkumarRTK
        Icon for Super User rankSuper User
        2 years ago

        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