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

[markdean]

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 🙂 

[markdean]

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

[Anonymous · Accepted solution]

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

[bhanu_gautam]

@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"
)

[markdean]

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 😞

[Anonymous]

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