Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
snaruma
Regular Visitor

Dynamic Quartile slicer

Hi,

 

I am trying to dynamically calculate quartiles for a certain column based on the selection of a calculated column.

 

So, I have a list of 100 account Ids with 2 columns (1) Normalized Electric Usage (Norm_Usage) and (2) Heating & Cooling Load/Square Footage (H&C Load/SFT). I have divided these 100 accounts into 4 quartiles (based on column 1 Normalized Electric Usage) by creating a calculated column using a DAX function.

 

Step1: Create 4 quartiles based on column Normalized Electric Usage. Each quartile (1, 2,3 and 4) have 25 accounts each. 

Quartile(Norm_Usage) =
var Q1=percentile.exc(Test[Norm_Usage],0.25)
var Q2=percentile.exc(Test[Norm_Usage],0.5)
var Q3=percentile.exc(Test[Norm_Usage],0.75)
return
if(Test[Norm_Usage]<Q1, 4, if(Test[Norm_Usage]>=Q1 && Test[Norm_Usage]<Q2,3,if(Test[Norm_Usage]>=Q2 && Test[Norm_Usage]<Q3, 2, 1)))

 

Once I have done that, I would like to be able to select and filter by the column "Quartile(Norm_Usage)" and have a query dynamically calculate 4 quartiles (based on column 2  Heating & Cooling Load/Square Footage (H&C Load/SFT) values) for the accounts that show up within each quartile.

 

Step 2: When I select Quartile 1 in the column Quartile(Norm_Usage), it has 25 accounts. Based on the Heating & Cooling Load/Square Footage (H&C Load/SFT) values of these 25 accounts, I want to dynamically calculate and group these 25 accounts into 4 quartiles using the similar DAX function used above.Thus the 25 accounts should be broken down into 1-4 quartile values based on the distribution. This is the part where I am stuck. How should I use a DAX function such that the quartiles are calculated dynamically based on the quartile value selection of the Quartile(Norm_Usage) column (values are 1, 2, 3 and 4).

 

Appreciate any help with this.

 

 

4 REPLIES 4
Anonymous
Not applicable

I also have this issue. I need a dynamic quartile slicer than can be used as dimesnion in a chart. The dimension should dynamically segment the data into 4 quarters based on values and the applied filters.

 

Has anyone found a solution? @Greg_Deckler 

 
 
Anonymous
Not applicable

Dynamic Quartile Example.png

snaruma
Regular Visitor

Hi,

 

I am trying to dynamically calculate quartiles for a certain column based on the selection of a calculated column.

 

So, I have a list of 100 account Ids with 2 columns (1) Normalized Electric Usage (Norm_Usage) and (2) Heating & Cooling Load/Square Footage (H&C Load/SFT). I have divided these 100 accounts into 4 quartiles (based on column 1 Normalized Electric Usage) by creating a calculated column using a DAX function. (I have attached a sample dataset)

 

Step1: Create 4 quartiles based on column Normalized Electric Usage. Each quartile (1, 2,3 and 4) have 25 accounts each. 

Quartile(Norm_Usage) =
var Q1=percentile.exc(Test[Norm_Usage],0.25)
var Q2=percentile.exc(Test[Norm_Usage],0.5)
var Q3=percentile.exc(Test[Norm_Usage],0.75)
return
if(Test[Norm_Usage]<Q1, 4, if(Test[Norm_Usage]>=Q1 && Test[Norm_Usage]<Q2,3,if(Test[Norm_Usage]>=Q2 && Test[Norm_Usage]<Q3, 2, 1)))

 

Step1.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Once I have done that, I would like to be able to select and filter by the column "Quartile(Norm_Usage)" and have a query dynamically calculate 4 quartiles (based on column 2  Heating & Cooling Load/Square Footage (H&C Load/SFT) values) for the accounts that show up within each quartile.

 

Step 2: When I select Quartile 1 in the column Quartile(Norm_Usage), it has 25 accounts. Based on the Heating & Cooling Load/Square Footage (H&C Load/SFT) values of these 25 accounts, I want to dynamically calculate and group these 25 accounts into 4 quartiles using the similar DAX function used above.Thus the 25 accounts should be broken down into 1-4 quartile values based on the distribution. This is the part where I am stuck. How should I use a DAX function such that the quartiles are calculated dynamically based on the quartile value selection of the Quartile(Norm_Usage) column (values are 1, 2, 3 and 4).

 

Step2.JPG 

Finally - this is how I would like my dataset to look like (it is a sample dataset filtered on quartile 2 of Quartile(Norm_Usage column):

Capture.JPG

 

 

Appreciate any help with this.

 

 

Greg_Deckler
Super User
Super User

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.

 

Sample data.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.