cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brabby_dabby
Frequent Visitor

How to I Create Quartiles based on Different Groupings?

Hello,

I have a dataset that looks like this:

TimeClass1Class2Subclass1Subclass2VendorSKURate

12

1REF11FDRA111.089
121REF11FDRA112.112
121REF11FDRA113.121
121REF11FDRB121.080
121REF11FDRB122.065
121REF11FDRC131.050
121REF12AA141.112
121REF12AB151

.131

121REF12AC161.054

I have a lot more Times, Classes, Subclasses, SKUs, and Vendors.

 

First, I want to calculate the first and third quartiles:

I did so by creating a measure by the following:

 

Quartile1 = SUMMARIZE(dataset, dataset[Class1],dataset[Class2],dataset[Subclass1], dataset[Subclass2],"Q1", PERCENTILE.INC(dataset[rate],0.25))

 

 

But I get the following error: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

I've tried similar measures also, but when it works, it's not grouping the Quartiles like I want it to.

 

The Ideal end result is to make a table that looks like this for all the different Times, Classes, and Subclasses:

TimeClass1Class2Subclass1Subclass2VendorSKURateQuartile1Quartile3IQRLower LimitUpper Limit
121REF11FDRA111.089.30.45.15

.075

.675
241REF11FDRA111.099.41.51.10.26.66
361REF11FDRA111.124.43.60.17.175.855
             

where

IQR = Quartile1 - Quartile3

Lower_Limit = Quartile1 - (1.5*IQR) and

Upper_Limit = Quartile3 + (1.5*IQR)

 

I would like to dynamically calculate the Quartiles for the different Time, Classes, and Subclasses.

 

My end goal is to look at a particular SKU and identify whether or not that SKU is below the Lower_Limit or above the Upper_Limit.

 

Thank you for your help. I am not as familiar with DAX and have been stuck on this for a while.

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @brabby_dabby ,

 

Not sure if I understand you correctly, but if you want a table you will need to use New Table feature.

1.PNG

And you will get the below result if you are using the first formula.

2.PNG

 

Best Regards,

Jay

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

2 REPLIES 2
v-jayw-msft
Community Support
Community Support

Hi @brabby_dabby ,

 

Not sure if I understand you correctly, but if you want a table you will need to use New Table feature.

1.PNG

And you will get the below result if you are using the first formula.

2.PNG

 

Best Regards,

Jay

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

brabby_dabby
Frequent Visitor

I've also tried:

Quartile1 = 
    CALCULATE(
        PERCENTILE.INC(dataset[rate], 0.25),
        ALLEXCEPT(dataset, dataset[Time], dataset[Class2], dataset[Subclass2]))

 It seems to be calculating quartiles, but it's not grouping it the way I want (quartiles of rates for each Time, Class, Subclass)

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!