Design suggestions

[average_joe]

Hi all,

I'm in need of some design help with a report I've put together.

A slicer with custom date periods allows users to select last 3, 6 and 12 months of data or a custom date range.  The report tracks the number of tasks and their status at the end of the month over time shown in a stacked column chart.  Users want a row of data below this table to show the number of tasks 'on hold' each month which, when clicked on, displays a table next to the chart with details of the task (I've left this out due to sensitive information).

This row of data is another stacked column chart, filtered for tasks with status on hold.  I did this so that it aligns with the chart above, particularly if different periods are selected, the columns and labels line up.  I've set the y axis maximum to 0 so that labels are aligned, especially when a month has a 0 value.  I also changed the background colour to match the bar colour otherwise there would be no background colour behind the 0.

The issue I'm facing is that now users want to see a green box for 0 values and red for other values.

I tried using a table but I have issues with sizing the columns, these need to line up with the monthly stacked column chart, especially since the number of months selected changes the sizing of the monthly bars.

How would you go about this using the stacked column?

Would I be better off using a different visual?

Note that the organisation I work for only approves the use of Microsoft custom visuals in Power BI reports.

[amitchandak]

@average_joe , the below,  purple bar, can be a table and there you can do conditional formatting.

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.
How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/...
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pan...

[average_joe]

Hi @amitchandak ,

The issue I have is lining up the 2 visuals.  The months of the column chart need to line up with the visual below, regardless of the date period selected by the user.

As a rough example, using a table with conditional formatting I can set this up like this for the view of last 6 months of data.

However, as soon as I select a different period such as last 3 months, the alignment between the column chart and the table are mismatched.

Using a column chart as the bottom visual (as per my first post) allows me to line up the values, however, I can't seem to figure out how to get the conditional formatting I'm after with this option.

So I'm trying to figure out:

1. What visual to use so that the monthly values between the top stacked column chart and the bottom visual (whatever that may be) are aligned
2. Formatting the values so that 0 shows up as a colour such as blue or green and anything else as orange or red.