Power BI - Count occurrences in IF statement correct per day, wrong over time

Question

I have a long nested IF-statement measure that I use to colour a heatmap that I have. (Green, Yellow and Red)

However, I want to reuse this (if possible) to instead calculated the percentage that is green.

The measure I created to generate the colours: (but with colourcodes instead)

ColorMeasure = 
VAR xValue = [Sold Average]
VAR yValue = [Open Average]
RETURN
IF(
    yValue = 1,
    IF(
        xValue < 171, 
        "Green",
        IF(
            ABS(xValue - 171) <= 0.1 * 171,
            "Yellow",
            IF(
                xValue > 171,
                "RED",
                BLANK()
            )
        )
    ),
    IF(
        yValue = 2,
        IF(
            xValue >= 172 && xValue <= 257,
            "Green",
            IF(
                xValue >= 172 * 0.9 && xValue <= 257 * 1.1,
                "Yellow",
                "RED"
            )
        ),
        IF(
            yValue = 3,
            IF(
                xValue >= 258 && xValue <= 490,
                "Green",
                IF(
                    xValue >= 258 * 0.9 && xValue <= 490 * 1.1,
                    "Yellow",
                    "RED"
                )
            ),
            IF(
                yValue = 4,
                IF(
                    xValue > 491,
                    "Green",
                    IF(
                        xValue >= 491 * 0.9,
                        "Yellow",
                        "RED"
                    )
                ),
                BLANK()
            )
        )
    )
)

So what I want to do it is something like this

Divide(_GreenCount,(_GreenCount+_YellowCount+_RedCount)) But not sure how to properly do the counts.

I've tried using something like:

VAR _Colors1 =
    ADDCOLUMNS (
        DISTINCT ( TableC[Hour] ),
        "@Color", [ColorMeasure]
    )
VAR _Colors2 =
    ADDCOLUMNS (
        DISTINCT ( TableY[Hour] ),
        "@Color", [ColorMeasure]
    )

VAR _Green = COUNTROWS ( FILTER ( _Colors1, [@Color] = "Green" ) )
VAR _All   = COUNTROWS ( _Colors2 )
RETURN
    DIVIDE ( _Green, _All )

This gives me the correct hours when I look at a per day basis. But when looking at a whole week it gives me wrong percentages. example, green/all for whole week:

10/16   = 63%
13/17   = 76%
11/17   = 65%
13/19   = 68%
7/21    = 33%
4/21    = 19%
7/17    = 41%

Meaning the whole week would be 65/128 = 51% But the result of the measure is 67%, which is confusing to me.

I'm not really sure where it's going wrong and how to fix it. Any suggestions? Thanks

Answer 1

Probably it is due to looking at it per hour, and when looking at it for beyond a day then the hours overlap and you lose the grain calculation. Hard to be sure without seeing your data table.

Anyways, see if this works for you (I'm assuming each row is an hour).

Your Measure = 
  DIVIDE(
    COUNTROWS(FILTER(TableC, [ColorMeasure] = "Green")),
    COUNTROWS(TableC)
  )

If your table isn't row per hour, then try this alternative:

Your Measure = 
  var tbl = 
    SUMMARIZE(
      TableC,
      [Date],
      [Hour],
      "Color", [ColorMeasure]
    )
  return
    DIVIDE(
      COUNTROWS(FILTER(tbl, [Color] = "Green")),
      COUNTROWS(tbl)
    )