Adding the Quartile summary

[Sri_phani]

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. 

[Sri_phani]

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]

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]

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. 

[Sri_phani]

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. 

[Sri_phani]

@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]

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

[Anonymous]

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.