Forum Discussion
Conditional Quadrant chart in PBI
Hi AartiD .
Thank you for the update. Please try below steps.
1. Quadrant Color measure for bubble coloring
Quadrant Color =
VAR QoQ = [QoQ Growth (Rolling Avg 3M) %]
VAR YoY = [HYoHY Growth (Rolling Avg 6M) %]
RETURN
SWITCH(
TRUE(),
QoQ < YoY && QoQ < 0 && YoY < 0 && QoQ < -0.1, "#FF0000",
QoQ > YoY && QoQ > 0 && YoY > 0 && QoQ > 0.1, "#008000",
QoQ < YoY, "#FFFF00",
QoQ > YoY, "#0000FF",
"#808080"
)
2. Instead of placing the Quadrat category measure in Legend , place the measure in Tooltip.3. Apply Conditional Coloring, Select the Scatter chart.
Go to Format --> Markers --> Colors --> fx (conditional formatting). Set Format style to Field value. Based on field: Quadrant Color. This will color the bubbles based on the quadrant logic.I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Hi AartiD ,
Thank you for reaching out to the Microsoft Community Forum.
You want to create Bubbles Chart with quadratant categories as "Watch Out", "Concern", "Improving" & "Good". And you have created Calculated column with some conditions, but getting below errors.
1) Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.
2) Ambigious path Error.
Please try below steps to get the expected output.
1. Instead of creating calculated column , create the measure with below DAX code.
Quadrant Category =
SWITCH(
TRUE(),
[QoQ Growth (Rolling Avg 3M) %] > 10 && [HYoHY Growth (Rolling Avg 6M) %] > 0, "Good",
[QoQ Growth (Rolling Avg 3M) %] > [HYoHY Growth (Rolling Avg 6M) %], "Improving",
[QoQ Growth (Rolling Avg 3M) %] < -10 && [HYoHY Growth (Rolling Avg 6M) %] < 0, "Concern",
[QoQ Growth (Rolling Avg 3M) %] < [HYoHY Growth (Rolling Avg 6M) %], "Watch Out",
"Other"
)
Note: Calculated columns evaluate row-by-row without time context, whereas measures can aggregate by hospital, period, etc.
2. Use the Scatter Chart visual instead of Bubble Chart , Scatter supports numeric X & Y axis.
X Axis --> [QoQ Growth (Rolling Avg 3M) %],
Y Axis --> [HYoHY Growth (Rolling Avg 6M) %]
Size --> [Total Sales]
Details --> Hospital Name
Legend --> Quadrant Category measure
Filters --> Year = 2025
3. Add Text Boxes or Cards, Use transparent text boxes for quadrant labels:
Top-right --> “Good”
Top-left --> “Watch Out”
Bottom-left -->“Concern”
Bottom-right --> “Improving”
Note: You saw errors because, Calculated columns can’t directly use time intelligence like rolling averages. You likely have multiple relationships between your date tables and sales, causing ambiguity. By keeping this logic inside a measure, Power BI will calculate the quadrant correctly per hospital and filter context.
Please refer below link.
Scatter, bubble, and dot plot charts in Power BI - Power BI | Microsoft Learn
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
I am not able to put Quadrat category measure in Legend even in Scatter chart.
Hi AartiD .
Thank you for the update. Please try below steps.
1. Quadrant Color measure for bubble coloring
Quadrant Color =
VAR QoQ = [QoQ Growth (Rolling Avg 3M) %]
VAR YoY = [HYoHY Growth (Rolling Avg 6M) %]
RETURN
SWITCH(
TRUE(),
QoQ < YoY && QoQ < 0 && YoY < 0 && QoQ < -0.1, "#FF0000",
QoQ > YoY && QoQ > 0 && YoY > 0 && QoQ > 0.1, "#008000",
QoQ < YoY, "#FFFF00",
QoQ > YoY, "#0000FF",
"#808080"
)
2. Instead of placing the Quadrat category measure in Legend , place the measure in Tooltip.3. Apply Conditional Coloring, Select the Scatter chart.
Go to Format --> Markers --> Colors --> fx (conditional formatting). Set Format style to Field value. Based on field: Quadrant Color. This will color the bubbles based on the quadrant logic.I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Hi AartiD ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
Hi @AartiD ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh