topic Re: Help: Struggling with DAX to show Quartile Position in a table in DAX Commands and Tips

Help: Struggling with DAX to show Quartile Position in a table

markdean — Mon, 03 Feb 2025 10:51:32 GMT

Hi all - I am hoping someone can help me write a DAX measure to show 'Quartile Position' within a table. This is where I'm at...

This is the DAX I've written so far:

Quartile =

VAR SelectedUnit =

CALCULATE ( 'MASTER_DATASET'[RANK])

VAR p25 =

PERCENTILEX.INC (

ALLSELECTED ( 'MASTER_DATASET'[Institution Name] ),

CALCULATE ( [RANK] ),

0.25

)

VAR p50 =

PERCENTILEX.INC (

ALLSELECTED ( 'MASTER_DATASET'[Institution Name] ),

CALCULATE ( [RANK] ),

0.5

)

VAR p75 =

PERCENTILEX.INC (

ALLSELECTED ( 'MASTER_DATASET'[Institution Name] ),

CALCULATE ( [RANK] ),

0.75

)

RETURN

SWITCH (

ISFILTERED('MASTER_DATASET'[Institution Name]),

SelectedUnit <= p25, "Q1",

SelectedUnit > p25 && selectedunit <=p50, "Q2",

selectedunit > p50 && SelectedUnit <= p75, "Q3",

"Q4"

)

It's the first time I've ever tried to show quartile position in a table so I'm struggling to work out how to resolve this, hence the plea for help! If anyone knows what tweaks I need to make to the above DAX to get this to work, then I'd really appreciate the input and advice. Thanks in advance - Mark 🙂

Re: Help: Struggling with DAX to show Quartile Position in a table

bhanu_gautam — Mon, 03 Feb 2025 10:53:38 GMT

markdean , Try using

DAX
Quartile =
VAR SelectedUnit =
CALCULATE ( 'MASTER_DATASET'[RANK] )
VAR p25 =
PERCENTILEX.INC (
ALLSELECTED ( 'MASTER_DATASET'[Institution Name] ),
CALCULATE ( [RANK] ),
0.25
)
VAR p50 =
PERCENTILEX.INC (
ALLSELECTED ( 'MASTER_DATASET'[Institution Name] ),
CALCULATE ( [RANK] ),
0.5
)
VAR p75 =
PERCENTILEX.INC (
ALLSELECTED ( 'MASTER_DATASET'[Institution Name] ),
CALCULATE ( [RANK] ),
0.75
)
RETURN
SWITCH (
TRUE(),
SelectedUnit <= p25, "Q1",
SelectedUnit > p25 && SelectedUnit <= p50, "Q2",
SelectedUnit > p50 && SelectedUnit <= p75, "Q3",
"Q4"
)

Re: Help: Struggling with DAX to show Quartile Position in a table

markdean — Mon, 03 Feb 2025 12:38:04 GMT

Hi bhanu_gautam , many thanks for replying and for the suggestion. Sadly, it's still only producing a column of 24 Q1 values and is not returning any Q2, Q3 or Q4 values 😞

Re: Help: Struggling with DAX to show Quartile Position in a table

Anonymous — Tue, 04 Feb 2025 01:34:07 GMT

Hi markdean ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Re: Help: Struggling with DAX to show Quartile Position in a table

markdean — Tue, 04 Feb 2025 12:51:59 GMT

Hi Anonymous , thanks for the reply. Ive included two links below, 1 for some dummy data and 1 for a pbix file that should show the issue 👍 The original DAX works when I remove all filters. The problem seems to occur when filters are applied. There are two tables in the attached pbix that will show what I mean...Thanks again everyone 🙂

Link 1: dummy data MarkDummyData.xlsx

Link 2: pbix Showing Quartiles Dummy Data.pbix

Re: Help: Struggling with DAX to show Quartile Position in a table

Anonymous — Wed, 05 Feb 2025 07:20:16 GMT

HI markdean,

I suppose they may relate to filter effect. If the filter effects applied on these records and only keep records that equal to current 'Institution Name' category, they should return wrong results due to calculation on the wrong ranges.

For this scenario, you can try to create a variable to stored filtered table records and ranks. Then you can use the max rank from the variable table to compare with current rank and use the result to assign group.

All the secrets of SUMMARIZE - SQLBI

Regards,

Xiaoxin Sheng