Custom Clusters for Scatter Chart from Measure?

[SevsBo]

I have a report that has two key columns:
- Size of company (revenue)
- Profit margin (automatcially calculated on a per-month basis based on other columns)

I've made a Scatter Chart to plot the relationship between the two and that works well, but I am struggling to build meaningful clusters from it. If I use the automated cluster option the results are not useful.

On the simplest level I would like to break down clusters into four categories based on Profit Margin:

- Negative

- Zero

- Zero to 10%

- Over 10%

These will have to be made in a measure as the classification will change month-on-month for each company and will depend on what length of time is selected.

I have used a combiation of variables to first calculate the Margin and then used the SWITCH formula to build the basic logic as a measure, but can only use it either as a separate column in a table, or as a tooltip on the Scatter Chart. It will not allow me to use it either as an Axis or, as I want to use it, as a Legend entry to visually split the data in the Scatter Chart.

Is there a way to achieve this?

[rajendraongole1]

Hi @SevsBo - Power BI doesn’t allow measures as legends because they operate on aggregate-level data rather than row-level data.

you can create a calculated column as legend as below:

Profit Margin Category Column =
VAR Margin = [Profit Margin]
RETURN
SWITCH(
TRUE(),
Margin < 0, "Negative",
Margin = 0, "Zero",
Margin > 0 && Margin <= 0.1, "Zero to 10%",
Margin > 0.1, "Over 10%"
)

Hope this works. please check

[SevsBo]

Hi @rajendraongole1 

I have thought about doing a calculated column instead but I don't think that would be possible. The data for Margin is calcualted by another measure, as it comes from monthly data. The Margin for one month might be positive, while on another month it could be negative, so the only solution I could find was to make a SUM measure that takes the time filter into account.

I don't believe a calculated column could produce a result that changes depending on how many months are selected and applies a single rating per company?

[rajendraongole1 · Accepted solution]

Hi @SevsBo  - Got it, You are correct that a calculated column would not be suitable in this scenario.

Create a static table with the clusters and it serves as a legend

Clusters =
DATATABLE(
"Cluster", STRING,
{
{"Negative"},
{"Zero"},
{"Zero to 10%"},
{"Over 10%"}
}
)

Create the Clustering Measure Here's an example of how to create the measure for clustering

Margin Cluster =
VAR Margin = [Margin Measure] -- Replace with your actual Margin measure
RETURN
SWITCH(
TRUE(),
Margin < 0, "Negative",
Margin = 0, "Zero",
Margin > 0 && Margin <= 0.1, "Zero to 10%",
Margin > 0.1, "Over 10%"
)

Use the Margin Cluster measure as a slicer or tooltip 

or another way is using the Margin Cluster measure to create a calculated color measure and apply it as a conditional format for the chart's color.

Hope this helps.