Conditional Quadrants in Bubble Chart-PBI

[AartiD]

Hi,

I want to create Bubbles Chart with quadratant categories as "Watch Out", "Concern", "Improving" & "Good". Below is screen shot of Bubble chart in Excel. X-Axis will have QoQ% Growth & Y-Axis will have YoY% Growth. Bubbles will be hospital Names and Size of Bubbles will be Total Sales.  Its for Current year -2025 only. I want Quadrants to be divided as per below conditions.

Categories	Conditions
Concern	QoQ<HYoHY and both negative; QoQ < -10%
Watch out	QoQ<HYoHY
Improving	QoQ>HYoHY
Good	QoQ>HYoHY and both positive; QoQ above 10%

I have used below measure for the above conditions:-

Quadrant for Bubble Chart=

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) %] < [HYoHY Growth (Rolling Avg 6M) %] , "Watch Out",

[QoQ Growth (Rolling Avg 3M) %] <= -10 && [HYoHY Growth (Rolling Avg 6M) %] >= 0, "Concern")

I am only able to create measure , if i create column for above measure i am getting below erros:-

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.

I have already created one Bubble chart  but it does not have above Conditional quadrant. Can anyone help with conditional quadrant?

[v-karpurapud]

Hi @AartiD 

Thank you for submitting your question to the Microsoft Fabric Community Forum.

The error occurs because a calculated column is being created instead of a measure, using DAX formulas that require time-intelligence measures. Remove the column and create a measure using the DAX provided by @grazitti_sapna . Once the measure is created, add it to the Legend field of your Bubble Chart. This approach will accurately categorize hospitals into the “Good,” “Improving,” “Watch Out,” and “Concern” quadrants without generating errors.

I hope this information is helpful. . If you have any further questions, please let us know. we can assist you further.

Regards,

Microsoft Fabric Community Support Team.
 

[grazitti_sapna]

Hi @AartiD,

Update your DAX as below

Quadrant Category :=
SWITCH(
TRUE(),
-- Good: both positive and QoQ > HYoHY, QoQ above +10%
[QoQ Growth (Rolling Avg 3M) %] > 0 &&
[HYoHY Growth (Rolling Avg 6M) %] > 0 &&
[QoQ Growth (Rolling Avg 3M) %] >= 10 &&
[QoQ Growth (Rolling Avg 3M) %] > [HYoHY Growth (Rolling Avg 6M) %],
"Good",

-- Improving: QoQ > HYoHY but not both strongly positive
[QoQ Growth (Rolling Avg 3M) %] > [HYoHY Growth (Rolling Avg 6M) %],
"Improving",

-- Concern: both negative and QoQ < HYoHY and below -10%
[QoQ Growth (Rolling Avg 3M) %] < 0 &&
[HYoHY Growth (Rolling Avg 6M) %] < 0 &&
[QoQ Growth (Rolling Avg 3M) %] < -10 &&
[QoQ Growth (Rolling Avg 3M) %] < [HYoHY Growth (Rolling Avg 6M) %],
"Concern",

-- Watch Out: QoQ < HYoHY (general case)
[QoQ Growth (Rolling Avg 3M) %] < [HYoHY Growth (Rolling Avg 6M) %],
"Watch Out",

-- Default
"Other"
)

• Add a Bubble Chart visual.

• Drag:

  • X-axis: [QoQ Growth (Rolling Avg 3M) %]

  • Y-axis: [HYoHY Growth (Rolling Avg 6M) %]

  • Size: [Total Sales]

  • Category (Legend): [Quadrant Category]

  • Details (Bubbles): Hospital Name

Add Quadrant Reference Lines

• In the Visual formatting pane, go to Analytics → X-axis Constant Line

  • Value = 0

  • Label = “YoY = 0”

• Add another Y-axis Constant Line

  • Value = 0

  • Label = “QoQ = 0”
    
    

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[AartiD]

I am getting below error while using above formula.

" Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."

[grazitti_sapna]

Okay @AartiD,

The reason could be One (or both) of your measures is using DAX time-intelligence function (like DATEADD, SAMEPERIODLASTYEAR, PARALLELPERIOD, etc.

Create a date table if uou've not created one

Date_Master =
ADDCOLUMNS(
CALENDAR(DATE(2020,1,1), DATE(2030,12,31)),
"Year", YEAR([Date]),
"Month", FORMAT([Date], "MMM"),
"Month-Year", FORMAT([Date], "MMM YYYY"),
"Quarter", "Q" & FORMAT([Date], "Q")
)

Go to Model view → Table tools → Mark as date table → Select [Date] column.

Ensure your fact table (e.g., Sales) has a relationship with Date table

Rebuild your measures like below

3-month rolling average (QoQ)

QoQ Growth (Rolling Avg 3M) % :=
VAR CurrentSales =
AVERAGEX(
DATESINPERIOD('Date_Master'[Date], MAX('Date_Master'[Date]), -3, MONTH),
[Total Sales]
)
VAR PrevSales =
AVERAGEX(
DATESINPERIOD('Date_Master'[Date], DATEADD(MAX('Date_Master'[Date]), -3, MONTH), -3, MONTH),
[Total Sales]
)
RETURN
DIVIDE(CurrentSales - PrevSales, PrevSales, 0) * 100

6-month rolling average (HYoHY)

HYoHY Growth (Rolling Avg 6M) % :=
VAR CurrentSales =
AVERAGEX(
DATESINPERIOD('Date_Master'[Date], MAX('Date_Master'[Date]), -6, MONTH),
[Total Sales]
)
VAR PrevSales =
AVERAGEX(
DATESINPERIOD('Date_Master'[Date], DATEADD(MAX('Date_Master'[Date]), -6, MONTH), -6, MONTH),
[Total Sales]
)
RETURN
DIVIDE(CurrentSales - PrevSales, PrevSales, 0) * 100

Now use these measures in final DAX

Quadrant Category :=
SWITCH(
TRUE(),
[QoQ Growth (Rolling Avg 3M) %] > 0 &&
[HYoHY Growth (Rolling Avg 6M) %] > 0 &&
[QoQ Growth (Rolling Avg 3M) %] >= 10 &&
[QoQ Growth (Rolling Avg 3M) %] > [HYoHY Growth (Rolling Avg 6M) %], "Good",

[QoQ Growth (Rolling Avg 3M) %] > [HYoHY Growth (Rolling Avg 6M) %], "Improving",

[QoQ Growth (Rolling Avg 3M) %] < 0 &&
[HYoHY Growth (Rolling Avg 6M) %] < 0 &&
[QoQ Growth (Rolling Avg 3M) %] < -10 &&
[QoQ Growth (Rolling Avg 3M) %] < [HYoHY Growth (Rolling Avg 6M) %], "Concern",

[QoQ Growth (Rolling Avg 3M) %] < [HYoHY Growth (Rolling Avg 6M) %], "Watch Out",

"Other"
)

Use in the view

• X-Axis: [QoQ Growth (Rolling Avg 3M) %]

• Y-Axis: [HYoHY Growth (Rolling Avg 6M) %]

• Size: [Total Sales]

• Legend: [Quadrant Category]

• Details: Hospital Name