Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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.

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

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

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

     

    Best Regards,

    Jay

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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)

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Anonymous ,

     

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

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

     

    Best Regards,

    Jay