Stacked bar chart with percentage conditional formatting

[Saud19999]

hey there,,

as shown below I want to format my stacked bar with the percentage conditional formatting, the issue happened by adding two X-axis measures in the visual..

is there a way to add conditional formatting while having two X-axis?

here is the BI file sample.pbix

thanks.

[MFelix]

Hi @Saud19999 ,

Do you need to have the white bar until the 100%? 

[MFelix]

Hi @Saud19999 ,

Do the following:

• Create a table with your categories of colours this needs to have two values one for the bar and another with the difference to 100%

• Add the following measure:

ClusterCalculation =
VAR _Valueto100 =
    1 - DIVIDE ( [Last day all clusters - tank], SUM ( Capacity[Capacity] ) )
RETURN
    IF (
        SELECTEDVALUE ( Capacity[Cluster] ) <> BLANK (),
        SWITCH (
            TRUE (),
            SELECTEDVALUE ( Categories[Subcat] ) = "Diff"
                && DIVIDE ( [Last day all clusters - tank], SUM ( Capacity[Capacity] ) )
                    - MIN ( Categories[Minimum] ) >= 0
                && DIVIDE ( [Last day all clusters - tank], SUM ( Capacity[Capacity] ) )
                    - MAX ( Categories[Maximum] ) <= 0, _Valueto100,
            IF (
                DIVIDE ( [Last day all clusters - tank], SUM ( Capacity[Capacity] ) )
                    - MIN ( Categories[Minimum] ) >= 0
                    && DIVIDE ( [Last day all clusters - tank], SUM ( Capacity[Capacity] ) )
                        - MAX ( Categories[Maximum] ) <= 0,
                DIVIDE ( [Last day all clusters - tank], SUM ( Capacity[Capacity] ) )
            )
        )
    )

• Make you chart with the follwing setup
  • Y-Axis: Cluster
  • X-Axis: ClusterCalculation
  • Legend: Category (from the new table)
  • Format the color for each of the group accordingly to your needs:

[Saud19999]

Thank you @MFelix  for you time and help.

It's working for only3 colors , whan there is a green or yelow case it does not show it.

is there anyway solution around it?

[MFelix]

In the example I created there was only 3 groups with data because when there a no values it returns blank, when you have more groups go to the colours and select the correct colour for the specific value.

If that does not work can you tell me the filters you are applying so I can get more than. 2 colors. 

[Saud19999]

thank you again for your fast answer,

your example is splendid, however, it has some blank values even though there are data (shown below).

and if you could please add the 2 colours your solution would be perfect 

also is there a way to sort the values based on their value? from highest to lowest.

thank you again 

[MFelix]

Hi @Saud19999 ,

Check the file updated attach.

[Saud19999]

great, now all colors are visible, but I still have an issue with blank values even though it's not empty..

also, the visual works excellent when there is a month selected but in my case, it's a trend for the whole year so I cant use filters

as you can see here a comparison with filter and without.

[MFelix]

Can you tell me wich one is correct? So I can check the calculations

[Saud19999]

any help ? @MFelix 

[MFelix]

Hi @Saud19999 ,

When you refer that you are adding two axis you mean you are using the legend to make the colours correct?

Power BI does not allow to have condittional formatting while using the legend or more than one measure on the values.

The file is asking for login can you share it again please.

[Saud19999]

yes, that's correct..

I tried opening the file and it opens without any problem, you can download it directly