I have a dataset that looks like this:
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:
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.
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.
View solution in original post
I've also tried:
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)
Check out the News & Announcements to learn more.
Congratulations, the new Super User Season 2 for 2021 has started!
Check out the new Power Platform Community Connections gallery!