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...
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


     

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.