Forum Discussion

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

Adding the Quartile summary

Hi, 

 

Can someone help me summarize the Quartiles that I created using a Measure. 

I have data that has the Customer Resolve Survey score and I created a Quartile using this measure: 

 

Res% Quartile =
VAR AgentScore = [Resolve]
VAR FilteredData = ALLSELECTED('1_Core Raw data'[Start of Month],'1_Core Raw data'[Partner & Site],'1_Core Raw data'[Team manager],'1_Core Raw data'[Final LOB],'1_Core Raw data'[agent_email], '1_Core Raw data'[File])
 
RETURN
   IF(NOT ISBLANK([Resolve]),SWITCH(
        TRUE(),
        AgentScore >= PERCENTILEX.INC(FilteredData, [Resolve], 0.75), "Q1",
        AgentScore >= PERCENTILEX.INC(FilteredData, [Resolve], 0.50), "Q2",
        AgentScore >= PERCENTILEX.INC(FilteredData, [Resolve], 0.25), "Q3",
        AgentScore >= PERCENTILEX.INC(FilteredData, [Resolve], 0.00), "Q4"
        ))
 
Now the requirement is, I have to show the number of agents and the max and min score based on the available table. Like I provided in the below snapshot. 
 

 

Here is glance of dataset that I have, I have single dataset which has columns such as Lyft ID, team manager, LOB, Date, Start of Month. All these I will use as a slicer to filter the visual. 

 

Kindly help me! Thanks. 

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Sri_phani ,

    I think you can create a new table to show the number of agents and the max and min scores for each quartile.

    Quartile Summary =
SUMMARIZE (
    '1_Core Raw data',
    [Res% Quartile],
    "Number of Agents", COUNT ( '1_Core Raw data'[agent_email] ),
    "Max Score", MAX ( '1_Core Raw data'[Resolve] ),
    "Min Score", MIN ( '1_Core Raw data'[Resolve] )
)

    You can also add slicer to filter the data.

     

     

     

    Best Regards

    Yilong Zhou

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

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

    Anonymous Thank you for responding. I am unable to group by  "[Res% Quartile]"

    Not just this, I am unable to group by using any of the measures.  

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Sri_phani ,

      I'm sorry I didn't achieve the results you expected, if you can provide me with specific example data or .pbix files, I can provide further help and answers.

       

       

      Best Regards

      Yilong Zhou

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

    Hi Anonymous , Please find the same file below. while I am unable to provide all the columns that can replicate exact columns as per the image. This data set is mostly a replica of the one I am working on. 

     

    Quartile file 

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Sri_phani ,

      I create a table as you mentioned.

      Then I create a measure and here is the DAX code.

      Measure = 
VAR _Resolve =
    MAX ( 'Table'[Resolve] )
RETURN
    IF (
        NOT ISBLANK ( _Resolve ),
        SWITCH (
            TRUE (),
            _Resolve >= 0.75, "Q1",
            _Resolve < 0.75
                && _Resolve >= 0.5, "Q2",
            _Resolve < 0.5
                && _Resolve >= 0.25, "Q3",
            _Resolve < 0.25
                && _Resolve >= 0, "Q4",
            BLANK ()
        )
    )

      I create a color DAX code and put it into the Measure column.

      Color = 
SWITCH(
    TRUE(),
    'Table'[Measure] = "Q1", "Green",
    'Table'[Measure] = "Q2", "Blue",
    'Table'[Measure] = "Q3", "Orange",
    "Red"
)

       

       

       

       

      Best Regards

      Yilong Zhou

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

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

        Hi Anonymous,

         

        The question is about summarizing the Quartile as a table, like this. Not about the conditional formatting. Can you please help with this.