Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |