Count cells with X background colour or X value

[jonbox]

Hi, hopefully a quick one!

I'm trying to create a measure that count cells based on their colour.

This is my current measure which as i mentioned SHOULD count the number of green cells in july for each MAGICS scorecard area for each department,  e.g. for inventory (RM/PM/WIP) it should count 2. For inventory FP it should count 2. FOr ITOT it should count 0, as these are red, and i only want to count green.

Totals Dpt = COUNTX(filter(allselected('Scorecard (3)'), [Department] = MAX([Department]) && [Magics Scorecard Area] = MAX([Magics Scorecard Area])),[Jul Value])

if this isn't possible, i also have another measure that uses an excel macro;

this macro represents the colours as numbers so that i can count that way, however i need a measure that count for department, for each magic scorecard area, each number represented as green, e.g. for department SYN, inventory (RM/PM/WIP) it should count two by counting the value in the Jul Value column.

any help with this would be vastly appreciated! Thank you.

[v-kelly-msft]

Hi  @jonbox ,

Could you pls provide some sample data with expected output for test?

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[jonbox]

Hi @v-kelly-msft and @Greg_Deckler ,

Apologies for the delay with my reply;  

in terms of sample data and output and data;

IF the JULY SCORE meets the GOALS column e.g. first and third row, the cells are coloured green.

IF the JULY SCORE does not meet the GOALS column e.g. second and fourth row, the cells are coloured red.

In the July total, I want the cells to tally up the over all greens compared to reds e.g. in my sample, green cell means 1, red means 0.

In my previous example i didn't have the july score column but thinking about it logically, it would make sense to have it.

Goals	July Score	July Total
0%	0%	1
0%	10%	0
> 95%	99%	1
100%	50%	0

Essentially i want it to count the number of greens and put it into a new column.

Hopefully that is clear and hope you can still help me with my issue!

Thanks a lot guys.

[v-kelly-msft]

Hi  @jonbox ,

Using below M codes to get the column "July Total":

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlBV0oEQSrE6UK4hgh9TamBgnGppChKwtIQLGxqAlZhCFcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Goals = _t, #"July Score" = _t, #"July Total" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Goals", type text}, {"July Score", type text}, {"July Total", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Select([Goals],{"0".."9","%"})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each let check=if Text.Length([Custom])<Text.Length([Goals]) then 1 else 0
in 
if check=1 then 
Text.Start([Goals],Text.Length([Goals])-Text.Length([Custom]))

else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [Custom.1]=null then if Value.Compare(Number.From([July Score]),Number.From([Custom]))=0 then 1 else 0
else if Value.Compare(Number.From([July Score]),Number.From([Custom]))=1 then ">" else if Value.Compare(Number.From([July Score]),Number.From([Custom]))=0 then "=" else "<"),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each if [Custom.1]=null then [Custom.2] else if Text.Contains([Custom.2],[Custom.1]) then 1 else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"July Total", "Custom.1", "Custom.2"})
in
    #"Removed Columns"

Then use color formatting to set the color of the value and you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[jonbox]

Hi kelly,

thanks so much for the help. i've integrated your code into my powerbi and have it almost working, however the code seems to fail when picking up decimal numbers in particular? 

As you can see below the "custom" column converts to 095 rather than 0.95 . assume that has something to do with it? although custom.2 seems to understand that the Jul column is greater than the goals column, so why does it show as 0 in custom.3?

Any help with this please?

Thanks again for your help

[Greg_Deckler]

@jonbox This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

If that doesn't help, would need sample data as text. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.