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
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.
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
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.