Forum Discussion
Helper IIICustom Message for Missing Values in Clustered Bar Chart
Super UserHi 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
shoeb1359It'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%"))
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
- 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
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
ThanksHi shoeb1359 ,
Please replace "null/blank" with "0" and try again.