Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a dataset that looks like this:
Time | Class1 | Class2 | Subclass1 | Subclass2 | Vendor | SKU | Rate |
12 | 1 | REF | 11 | FDR | A | 111 | .089 |
12 | 1 | REF | 11 | FDR | A | 112 | .112 |
12 | 1 | REF | 11 | FDR | A | 113 | .121 |
12 | 1 | REF | 11 | FDR | B | 121 | .080 |
12 | 1 | REF | 11 | FDR | B | 122 | .065 |
12 | 1 | REF | 11 | FDR | C | 131 | .050 |
12 | 1 | REF | 12 | A | A | 141 | .112 |
12 | 1 | REF | 12 | A | B | 151 | .131 |
12 | 1 | REF | 12 | A | C | 161 | .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:
Time | Class1 | Class2 | Subclass1 | Subclass2 | Vendor | SKU | Rate | Quartile1 | Quartile3 | IQR | Lower Limit | Upper Limit |
12 | 1 | REF | 11 | FDR | A | 111 | .089 | .30 | .45 | .15 | .075 | .675 |
24 | 1 | REF | 11 | FDR | A | 111 | .099 | .41 | .51 | .10 | .26 | .66 |
36 | 1 | REF | 11 | FDR | A | 111 | .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.
Solved! Go to Solution.
Hi @brabby_dabby ,
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
Hi @brabby_dabby ,
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
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)
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |