Forum Discussion

SevsBo's avatar
SevsBo
Responsive Resident
1 year ago
Solved

Custom Clusters for Scatter Chart from Measure?

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 th...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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.

rajendraongole1's avatar
rajendraongole1
Super User
1 year ago

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's avatar
    SevsBo
    Responsive Resident
    1 year ago

    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's avatar
      rajendraongole1
      Super User
      1 year ago

      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.