Forum Discussion

AartiD's avatar
AartiD
Icon for Helper II rankHelper II
11 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...
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


     

grazitti_sapna's avatar
grazitti_sapna
Icon for Super User rankSuper User
11 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
    Icon for Helper II rankHelper II
    11 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
      Icon for Super User rankSuper 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