Count no. of responses by Color in a Pivot Table

[mrthomasshelby]

Hello, I have a table in an app as shown in the image below:

The cells have been colored based on a number of conditions being met or unmet.  Now I want to add in a measure in the dimension giving the number of 'Green'  '/'  'Total Filled Cells' for each supplier. For example, for Supplier 1, there are 5 green colored cells out of 6 cells which have valid responses(responses other than 'N/A'). So, the dimension for supplier 1 should display 5/6. Similarly Supplier 2 would be 1/6. Can anyone please help me out with the proper expression for this as it appears to be complex due to the multiple conditions for coloring the cells and I'm unable to build a correct expression. Thanks in advance!

stalwar1‌ kaushik.solanki‌ fvelascog72‌ Kindly help me out with this one. I have tried writing an aggregated Count If Function with the If Conditions for the background colors but it doesn't work.

[sunny_talwar · Accepted solution]

Something like this?

[mrthomasshelby]

Wow. This is exactly what I was looking for a long time Sunny. Amazing as usual. Can you maybe briefly explain the logic of the If statement you've written? Like what do the 0s and 1s at the end mean and how they get the count of passed cells? Thanks a lot Sunny! As always you've been a huge help!

[sunny_talwar]

If statement is the same as the color expression.... for numerator, I assigned all greens as 1 and every other color as 0. For denominator, Green, Yellow and Red were assigned 1 and every other color as 0. Sum the 1s in the numerator and denominator and you get what you were looking for

[mrthomasshelby]

Wow. I would have never ever got that idea. Guess this is how you learn. Thanks a lot again Sunny!

[sunny_talwar]

You got it