Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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)))
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).
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):
Appreciate any help with this.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |