Group before calculating the Quartiles

[carter]

Hello,

I am looking to group my data by one of the columns and then calculate the IQR of a corresponding column. The goal will be to find Q1 and Q3 values for each group then use this to filter outliers in the dataset.

To return the q1 for each group I tried 

Q1 = SUMMARIZE(Table1,[groupedColumn],"q1", PERCENTILE.INC(Table1[valueColumn], 0.25))

and recieved the error:

'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.'

The end goal will be to Filter all values < Q1-(IQR * 1.5) and  > Q3 + (IQR * 1.5)

Any help would be greatly appreciated. Thanks in advance!

[TomMartens]

Hey,

can you please provide some sample data how your data in the table Table1 looks like?

Cheers

[carter]

Some sample data can be seen here, with the groups in the column on the left and the values on the right

Thanks!

[TomMartens]

Ah,

I see ...

This will become a little more complex, currently I'm in a hurry. So it will take ~ 1.5 I will post again 😉

[TomMartens · Accepted solution]

Hey,

I'm sorry for the delay, and I'm also sorry for mentioning it would be become a little more complex ... At least not now.

I created a little pbix file, and I used the same the formula like you do, except a minor issue. Here is my formula

summarize to percentile = 
    SUMMARIZE('samplePercentile','samplePercentile'[GroupColumn],"p", PERCENTILE.INC('samplePercentile'[Value],0.25)
    )

And all works well, I also tried the formula on a real-life dataset, the same result, working well ...

I'm wondering if your [groupedcolumn] is a measure or column in Table1

So, I guess you have to share your data, being able to reproduce your issue (at least trying).

By the way, what version of Power BI Desktop are you using.

I'm using the latest July update: 2.48.4792.721

Regards

[Brainfried88]

Hi @TomMartens , what's the difference if it was a measure or column in a table? I think I'm encountering the same thing, but in my case, I need to rely on another measure. Unable to see the pbix file now as its been removed. Appreciate any replies!

[carter]

It's working now. Thanks!