topic Re: Adding the Quartile summary in DAX Commands and Tips

Adding the Quartile summary

Sri_phani — Tue, 02 Jul 2024 14:44:06 GMT

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.

Re: Adding the Quartile summary

Anonymous — Wed, 03 Jul 2024 03:48:09 GMT

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.

Re: Adding the Quartile summary

Sri_phani — Wed, 03 Jul 2024 10:38:14 GMT

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.

Re: Adding the Quartile summary

Anonymous — Mon, 08 Jul 2024 07:10:49 GMT

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

Re: Adding the Quartile summary

Sri_phani — Mon, 08 Jul 2024 10:46:26 GMT

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

Re: Adding the Quartile summary

Anonymous — Tue, 09 Jul 2024 09:36:55 GMT

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.

Re: Adding the Quartile summary

Sri_phani — Tue, 09 Jul 2024 16:17:01 GMT

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.

Re: Adding the Quartile summary

Sri_phani — Tue, 09 Jul 2024 16:17:02 GMT

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.