Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
Hey,
can you please provide some sample data how your data in the table Table1 looks like?
Cheers
Some sample data can be seen here, with the groups in the column on the left and the values on the right
Thanks!
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 😉
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
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!
It's working now. Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |