Forum Discussion

AartiD's avatar
AartiD
Helper II
10 months ago
Solved

Conditional Quadrants in Bubble Chart-PBI

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?

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • grazitti_sapna's avatar
    grazitti_sapna
    10 months ago

    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


     

6 Replies

  • grazitti_sapna's avatar
    grazitti_sapna
    Super User
    10 months ago

    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's avatar
      AartiD
      Helper II
      10 months ago

       

       

      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's avatar
        grazitti_sapna
        Super User
        10 months ago

        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


         

  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    10 months ago

    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.
     

  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    10 months ago

    Hi AartiD 

    I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.

    Thank You.

  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    10 months ago

    Hi AartiD 

    We have not received a response from you regarding the query and were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

     

    Thank You.