Conditional Formatting by Total Average

[Anonymous]

Good day! 

I am new to Power BI, and I must say, I really enjoy the learnings in this community.

As much as I tried to find any solution to my issue, I can't make the conditional formatting work.

I'm trying to get the conditional formatting work based on the average of two sites. Individually, conditional formatting works fine like a charm, however, if we look at the average, the conditional formatting is not working properly.

On the screenshot, you will see that the combined score of two sites are not properly working, despite the fact that conditional formatting works fine individually.

Any help is really appreciated. Thank you in advance.

[v-janeyg-msft]

Hi, @Anonymous 

I agree @Pragati11 . If you still need help, I believe the fastest way is to provide a sample file and your desired result. You need to make your logic clear, because you have been talking about your approach but the description is not clear, so it's difficult for us to see your problem.🙂

Best Regards,

Community Support Team _ Janey

[Pragati11]

Hi @Anonymous ,

Can you share your conditional formatting rules here? A screenshot will help from Power BI.

Also I am not able to understand this statement of your's - On the screenshot, you will see that the combined score of two sites are not properly working, despite the fact that conditional formatting works fine individually.

I can't see any totals in the screenshot that could be a reason why the above statement is not clear.

Can you try adding more details here?

Thanks,

Pragati

[Anonymous]

Hi, @Pragati11 

Thank you for checking on this. 

For the conditional formatting, please see below screenshot:

I added a column for me to determine the background colour to check whether the sites are able to hit their target or not. 

For the issue, I apologise if the screenshot confused you. It goes like this: The weighted average of the scores for two sites is shown on the initial screenshot. Individually, the conditional formatting is working fine for both sites (please see screenshots below)

Site 1

Site 2

However, if we get the average scores for the metrics the sites have in common, the conditional formatting is not working as expected:

Combined scores of two sites:

I hope this helps. 

[Pragati11]

Hi @Anonymous ,

First thing what I understand here that your conditional formatting is based on a text column, not a numeric metric like volume/proportions/average.

Second thing, the 3rd screenshot is still confusing me with respect to the combined score you are talking about. But let's keep this aside. 🙂

What happens when you move your Pass/Fail  column to your 3rd screenshot?

Do have few cases where you get PASS and FAIL values for same SITE ?

Basically multiple rows for a SITE with different PASS/FAIL values.

This could be one of the reason why the conditional formatting may not be working properly.

Because your conditional formatting rule takes into account FIRST value of your calculated column (summarisation type) that you have used for applying conditional formatting on ACTUAL column.

Thanks,

Pragati

[Anonymous]

Hi @Pragati11 

Thanks for replying. Sorry for taking you this long to understand my issue. 😞

For the conditional formatting i used, the text value is to identify the color that I use for background colour. So, if the metric exceeds the target, the column will get green, otherwise, red.

Basically, it is determined by the score of the site per metrics, please see screenshot of the excel raw I used (changed the site due to confidentiality)

For your questions, please see in-line reply: 

What happens when you move your Pass/Fail  column to your 3rd screenshot? - Nothing happens.

Do have few cases where you get PASS and FAIL values for same SITE ? - No, the formula is cell-based, however, there are instances that two sites are getting Pass and Fail for the same metrics.

I'm not sure if we can do the computation based on metric instead or based on value? if yes, can you please show me how to do it and apply it on the PBI?

Thank you in advance. 🙂

[Pragati11]

Hi @Anonymous ,

Is it possible to share the pbix file for the above scenario? It is still hard to understand your query completly.

Make sure you remove any sensitive information from your pbix file.

You can also create sample pbix file for the above scenario, if the data is sensitive and can't be shared.

For sharing the pbix file, follow the steps:

• Upload the pbix file to dropbox
• Share the dropbox link here

Thanks,

Pragati