Custom Message for Missing Values in Clustered Bar Chart

[shoeb1359]

Hello Experts,

I want to display custom message for missing value in Clustered Bar Chart. For Calculating values I am using measure.

Category	SubCategory	Profit
Category A	A1	100
Category A	A2	200
Category A	A3	300
Category B	B1
Category B	B2
Category C	C1	600

Thanks

[danextian · Accepted solution]

Hi @shoeb1359 

Assuming that's how really your table is, you can replace blanks with zero and then use custom label.

Custom Label =
IF (
    ISBLANK ( [Sum of Profit] )
        || [Sum of Profit] = 0,
    "no available data",
    FORMAT ( [Sum of Profit], "$#,#" )
)

you can use conditional formatting on label font color to make it red for those without data

[danextian · Accepted solution]

Hi @shoeb1359 

Assuming that's how really your table is, you can replace blanks with zero and then use custom label.

Custom Label =
IF (
    ISBLANK ( [Sum of Profit] )
        || [Sum of Profit] = 0,
    "no available data",
    FORMAT ( [Sum of Profit], "$#,#" )
)

you can use conditional formatting on label font color to make it red for those without data

[shoeb1359]

Project	Developer	Capacity	Completed Work
Project A	Developer 1	12	10
Project A	Developer 2	12.5	15
Project B	Developer 1	13	2.5
Project B	Developer 3
Project C	Developer 4
Project D	Developer 2	10	11

Hi ,

I have attached my screenshot from sample file. Not sure what i am missing

Thanks 

[v-priyankata]

Hi @shoeb1359 ,

Please replace "null/blank" with "0" and try again.

[shoeb1359]

It's not working in my Case 

In sample data we are referring whole number and single column but in real data it's Avg % Utilization which is basically based on 2 columns. 

Average Utilization = AVERAGEX('Test 1',DIVIDE(SUM('Table'[Completed Work]),SUM('Table'[Capacity (Hours)])))

Custom Label : 

if(ISBLANK(Average Utilization) || Average Utilization= 0, "no available data", FORMAT(Average Utilization), "0.0%"))

[v-priyankata · Accepted solution]

Hi @shoeb1359 ,

We are grateful for your participation in the Microsoft Fabric Community forum.
As per requirement addressing your query.

• As you mentioned, "it’s Avg % Utilization, which is basically based on 2 columns":
• I have taken the sample data below and created a custom column (Average Completed Work and Capacity), i.e., the average of two columns.

• Create a measure

**********************************************************

Utilization = DIVIDE(

    SUM('Sheet1'[Completed Work]),

    SUM('Sheet1'[Capacity (Hours)]),

    BLANK()

)

**********************************************************

• Create another measure for custom message

**********************************************************

Custom Message =

IF(

    ISBLANK([Utilization]) || [Utilization] = 0,

    "No available data",

    FORMAT([Utilization], "0.0%")

)

**********************************************************

• Now, select the Clustered Bar Chart. Drag "Task ID" to the Y-axis and drag the Custom column (Average Completed Work and Capacity) to the X-axis.
• Then, go to the Format pane > Data labels and enable the Data Labels. In the Data labels, enable the value option and add the field "Custom Message" as below. You can also change the font size and color.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Thanks,
Priyanka.

CST community member.

[shoeb1359]

Follow the same , Still not working .

Can you share your pbix file. Wanted to cross check formatting part .you forgot to mention here

[v-priyankata]

Attached!

[danextian]

As the proposed solutions are based on the sample data, the sample should always represent the actual data and is not overly simplified.

[shoeb1359]

As I have dynamic data it seems it's not worked. I tried but no luck.

Please share file if you did at your end

[VahidDM]

Hi @shoeb1359 

You can't directly show a custom message inside a clustered bar chart for missing values. Bar charts only display numeric values, not text messages.

Workarounds:

1. Card Visual for Messages:

   • Create a measure that checks if data is missing (e.g., returns TRUE if no data).
   • If data is missing, display a separate card visual with your custom message.
   • If data exists, the card remains blank or hidden.

2. Placeholder Values:

   • Instead of returning BLANK() from your measure, return a small placeholder value (e.g., 0.0001).
   • Use conditional formatting to color bars differently for "no data" scenarios.
   • Add a legend or tooltip explaining that a very small bar indicates missing data.

These methods help convey messages for missing values indirectly, since bar charts cannot directly display text-based messages in place of numeric values.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube