Calculating quartiles using a slicer and a new measure - how to do this?

[misterlau14]

Hi there, I am hoping to receive some assistance please with a problem I am currently unable to solve.

All relevant information (including links to access the Excel and Power BI files I'm using) are provided below

Background/dataset description

I am currently using a dataset containing 2 variables (ID & Language), each row represents a completed booking

Visual created (table)

I have created a table visual with 3 variables displayed as columns: ID, Language, ID count (this is a new measure I created, more on this below)

New measure created (ID count)

This new measure counts the number of completed bookings for each ID

DAX code for this new measure is: ID count = COUNT(Sheet1[Language])

Slicer visual

I have added a slicer which uses the variable Language 

The information displayed in the table visual will therefore change depending on the language that is selected in the slicer

What I want to calculate

Let's say that I hypothetically select a language from the slicer

In the table visual, I want to calculate quartiles using the numbers displayed in the ID count column

I would ideally want to have a new 4th column in my table visual named Quartile

In the Quartile column, each row will have a different value (ie. Quartile 1, Quartile 2, Quartile 3, Quartile 4) depending on the value that is in the ID count column

Screenshot of my ideal output

Excel + Power BI files (see links below)

Excel data 

Power BI - sample file 

Hopefully there are no issues with accessing either the Excel or Power BI files linked above

Thank you 🙂

[misterlau14 · Accepted solution]

Hi lbendlin, thank you for taking the time to provide a response to my question. 👍
Yes, creating a new measure was definitely the way to go instead of creating a calculated column (given that I created a measure to calculate the number of bookings for each ID).

I've had a look at your uploaded Power BI file and unfortunately the quartiles don't quite match up with my Excel output (have pasted screenshots of both Excel and Power BI below)

Workaround (ended up creating a new column)
I have since implemented a workaround by creating a different dataset in Excel that counts the number of bookings broken down by language for each ID (as some IDs can service more than 1 language)

Turns out that I needed to create a new identifier variable in my dataset to ensure that I had the correct count of the number of bookings broken down by language for each ID. I combined ID and Language to form this new identifier variable (e.g. 30013_ARABIC)

Next step was importing the updated dataset into Power BI and creating a table visual with the same columns

Creating a new column
I created a new column named Quartile and used the following DAX code (which worked with what I was trying to create)

Quartile =

var quartile_1 =

CONVERT(

    CALCULATE(

PERCENTILE.INC('COMPLETED INTERPRETER'[CONCAT count],0.25),

ALLEXCEPT('COMPLETED INTERPRETER','COMPLETED INTERPRETER'[Language])

),DOUBLE)

var quartile_2 =

CONVERT(

    CALCULATE(

PERCENTILE.INC('COMPLETED INTERPRETER'[CONCAT count],0.50),

ALLEXCEPT('COMPLETED INTERPRETER','COMPLETED INTERPRETER'[Language])

),DOUBLE)

var quartile_3 =

CONVERT(

    CALCULATE(

PERCENTILE.INC('COMPLETED INTERPRETER'[CONCAT count],0.75),

ALLEXCEPT('COMPLETED INTERPRETER','COMPLETED INTERPRETER'[Language])

),DOUBLE)

return SWITCH(

    TRUE(),

    CONVERT('COMPLETED INTERPRETER'[CONCAT count],double) <= quartile_1 , "Quartile 1" ,

    CONVERT('COMPLETED INTERPRETER'[CONCAT count],double) >= quartile_1 && CONVERT('COMPLETED INTERPRETER'[CONCAT count],double) <= quartile_2 , "Quartile 2" ,

    CONVERT('COMPLETED INTERPRETER'[CONCAT count],double) >= quartile_2 && CONVERT('COMPLETED INTERPRETER'[CONCAT count],double) <= quartile_3 , "Quartile 3" ,

    CONVERT('COMPLETED INTERPRETER'[CONCAT count],double) > quartile_3 , "Quartile 4"

)

The output in my new table is in the screenshot below

Thanks again for your help though - much appreciated 

[lbendlin]

You cannot create calculated columns from user input. Has to be measures all the way.

see attached