Colour points in scatterplot using a calculation

Question & Answer
, , ,
1

Hello!

So I’ve got the following dataset:
image

And I want to create a scatter plot of Categories and point to Categories that meet my criteria (I say Categories, because initially I thought it does work with Products, but no):

AVG([Coolness])>3 and AVG([Cost])>130

So I created a scatter plot using Categories as label:

image

and now I created a test to find the ones, that are of interest:
image

I try to use that in color, but this is the message I get:

image
image1161×569 59.3 KB

And this really makes me wonder. I can create a table that presents all the info that would be used in the scatter plot without any issues:
image

What is more, I can even filter the scatter plot using this test calc, so I understand this info is present in the visual:

image
image839×622 34.6 KB

I created this simplistic example just to show the point, but this limitation prevents a lot of designs and my clients won’t believe that such a setup is not possible with a BI tool. (I got a similar screenshot from google sheets as a proof that this is possible, but I don’t know the setup there)
image

Is there maybe a way around it? The only thing that comes to my mind is to materialize the calculation, but it won’t work in dynamic situations.

If this is not possible - “dynamic” categorizing using this calculation in this scenario, inside QS, not using SQL - could you please let me know?

Dashboard below:
Custom

Thanks a ton team :slight_smile:

1 Like
2

Hello @Filo, what are you wanting the color by field to represent? If you utilize the portion of the calculated field that you wrote, place it within an ifelse statement, you can use it in the color by. I’ll write an example below:

ifelse(avgOver(Coolness, [Category], PRE_AGG)>3 AND avgOver(Cost, [Category], PRE_AGG)>130, "Good", "Bad")

I tested this in your Arena view and it seems to be working as expecting. This would give you a way to return some options that you could then label your plotted points with. I’ll mark this as the solution, but please let me know if you have any questions. Thank you!

1 Like
3

Thanks a lot!! It works!

1 Like