Forum Discussion

andrea100's avatar
andrea100
Helper I
1 year ago

Problems implementing conditional formatting on bar chart with grand total count measures

Hi,

I've posted a question related to this before(https://community.fabric.microsoft.com/t5/Desktop/Conditional-formatting-on-bar-chart-for-each-bar-Is-this/m-p/4257232#M1337717) I was able to learn how to create conditional formatting on a bar chart depending on a measure. After doing so I realized I was getting the wrong result and that my measures were wrong. I'm unable to figure out how to implement this correctly. For background: I am trying to create a type of bar chart, where each bar may be coloured depending on measures.

Below is an example of the bar chart.

Figure 1

I would like to do this:

  1. The bar "Below" will be red if the grand total of Below is above 15%
  2. The bar "Marginal" will be yellow if the grand total of (Marginal + Below) is above 40%
  3. The bar "Meets" will be orange if is a certain %
  4. The bar "Exceeds" will be green if is a certain %

 

In my previous post I used measures I created (S1,S2,S3,S4) as the conditional formatting measures. They were based on the values of Below, Marginal, Meets and Exceeds. I realized there was a problem when all of the bars were meeting the conditional rules. To simplify the problem and investigate, I created measures Below%, Marginal%, Meets%, Exceeds% which calculated the grand total count separately. 


I noticed that the values changed depending on the view. If I put all of the fields that were in Figure 1 into this table, it would show the expected output:

But when I put it into another table it would show 100% for the fields:

After this I realized that is why all the conditions were meeting, it was because they were all showing as 100%. In order to calculate the PerformanceLevel, I did a grand total count. The PerformanceLevel values are string values. Is this because some of the related fields are not in its context? Or I am calculating the grand total count of each incorrectly? I have been trying to figure out this issue for a long time now but have not been able to figure it out. To calculate Below% and so on I am using these calculations:

 

Count BelowAverage = COUNTROWS(Filter('Table','Table'[PerformanceLevel]="Below Expectations"))

 

Below% = [Count BelowAverage]/COUNTROWS('Table')*100

 

To summarize, I'm looking for some guidance on how to calculate these grand total count measures correctly so that I can use them to do conditional formatting on a bar chart. I would appreciate any input and help on this. Please let me know if any clarifications need to be made. 

 

Need Help

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi andrea100 ,

    Use the ALL function to wrap the parameter table in COUNTROWS.

    In this way, the COUNTROWS function is not filtered by the visual rows field and thus returns all rows of the table.

    Below% = [Count BelowAverage]/COUNTROWS(ALL('Table'))*100


    The final result is as follows and hope it will meets your needs.

     

    Please see the attached pbix for reference.

    Best Regards,
    Dengliang Li

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

     

    • andrea100's avatar
      andrea100
      Helper I
      1 year ago

      Hi, thank you for looking into this. I tried, but it gives me the output 0.00% when I use ALL

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi andrea100 ,

    Is my follow-up just to ask if the problem has been solved?

    If so, can you accept the correct answer as a solution or share your solution to help other members find it faster?

    Thank you very much for your cooperation!

  • andrea100's avatar
    andrea100
    Helper I
    1 year ago

    Hello community, I am still having issues implementing this. I've been playing around with the solution that was accepted, but I noticed that in some cases the conditional formatting doesn't work as expected (cases where there is a sum). I noticed it will not take the sum into account.

     

    I am using this solution for conditional formatting:

    Color = 
SWITCH(
    TRUE(),
    [Below%]>15,"red",
    [Marginal%]+[Below%]>40,"yellow",
    [Meets%]+[Exceeds%]>90,"orange",
    [Exceeds%]>30,"green"
)

     

    I have many instances where Marginal+Below>40 and Meets+Exceeds>90 but the bar doesn't change colour. 

     

    Help and guidance is much appreciated.