Calculating sum/count of nested if satements

[thne123]

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)

Spoiler

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 I'm not sure how to do the _GreenCount

I've tried using something like: 

SUMX(
'TableA',
IF([ColorMeasure] = "Green", 1, 0)
)

But this just gives me 0 😕 

worth noting maybe is that xValue and yValue come from two different fact tables

[AlexisOlson]

Depending on the dimension you want the percentage over:

Green % =
VAR __Colors =
    ADDCOLUMNS (
        DISTINCT ( Table[Dimension] ),
        "@Color", [ColorMeasure]
    )
VAR __Green = COUNTROWS ( FILTER ( __Colors, [@Color] = "Green" ) )
VAR __All   = COUNTROWS ( __Colors )
RETURN
    DIVIDE ( __Green, __All )

[thne123]

@AlexisOlson 

Thanks for the reply and assist!

I've tried this suggestion but I can't get it right, 

I've tried a various number of Table[Dimension] but none seems to get it working.

The issue I have is that it just returns BLANK when it's a red or yellow hour. 

I've tried using the ColorMeausure I wrote in the original post to verify that it produces the expected result and it does. 

But the "Green %" measure returns the following: 

So only the green numbers appear. 

Which I actually want to display through a card visual, but that just says "Blank"

[thne123]

@AlexisOlson 

If I have filter for one store, and one week and I take out the _All from the calculation you sent. 

I get the result of "24", which makes sense since there are 24 hours in a day. But not 24 hours in a week. 

In fact I expect the number of hours to be 149, since that's how many hours we're open in that week. 

So I think the issue is that I'm using the wrong dimension here:

VAR __Colors =
    ADDCOLUMNS (
        DISTINCT ( Table[Dimension] ),
        "@Color", [ColorMeasure]
    )

because the dimension I use, which is the one I also use in the visualisation only have 24 hours. 

BUT, none of the other I've tried work. 

Not the fact table with all the xValue, not the fact table with yValue, or the date table... 

If I in my matrix select just one day, it shows the correct number of open hours with "_All"

If I then try "_Green" instead, it gives me a BLANK, unless I choose one specific hour, then it works...

[v-linyulu-msft]

Hi,@thne123 

 Has the problem been solved? If it does, share your solution and accept it as a solution that will help other community members who have the same problem as you.

Best Regards,

Leroy Lu

[AlexisOlson · Accepted solution]

Yep. The dimension is the issue.

I think you need something like

VAR __Colors =
    ADDCOLUMNS (
        SUMMARIZE ( TableA, TableA[Date], TableA[Hour] ),
        "@Color", [ColorMeasure]
    )

I don't know exactly what tables and columns you have, so you need to adjust the SUMMARIZE columns for your specific model.

[thne123]

@AlexisOlson 

Sorry for the suuuuper late reply, I went on summer vacation

Yes, that sort of solved the issue, it's now calculating almost correctly. 

It is counting the correct number of green and total hours. BUT, for the whole selected period (like a month). But, in the heatmap I have that visualizes the greens and red in the original post, it's based on a weekly average. So, this means that the percentages that's calculated is not accurate to the heatmaps that's week based. 

I do have a column for weeknumber, that I tried to add in the var __Colors above, but it's not changing the results. 

[AlexisOlson]

I wouldn't expect adding week number to help. Try recplacing TableA[Date] with TableA[WeekNumber].

[thne123]

@AlexisOlson 

Yeah that's what I meant, sorry

But it didn't give me the correct result, 

for the example below:

there is 131 filled boxes, 52 of which are green. Therefore I want to calculate 52/131 = 40% (39,6)

With TableA[Date] in the measure, it counts 240/556

with TableA[WeekNumber] (instead of date) it gets down to 102/110

[AlexisOlson]

I can't tell what's going wrong from information I see in this thread.

Can you share a link to a .pbix? If not, at least specify exactly what your full DAX is and exactly what columns you're using in your matrix visual.

[thne123]

@AlexisOlson 

Yeah it's quite a complicated page

I'll do my best to explain. The heatmap has numbers ranging from 1-4, this is an average of TableCount[Count], this table has one row per hour per day that a store is open. And gives a number 1-4 based on a certain criteria.

CALCULATE(
          ROUND( AVERAGE(TableCount[Count]), 0 ),
          TableCount[Count] <> 0
          )

In the heatmap:

Rows is [Hour] column from dimension "Days"

Column is WeekDayName from dimension "Date"

and then measure above for values. 

This step is correct and verified

The colours come from a very long if-statement measure that gives the colour based on another measure and depending if the matrix has 1 or 2 etc. 

So for example, if sales is 100, then "[Count]" should be 1. and if that's the case in the matrix it gets a green colour.

This is also correct and verified

What I want to do is calculate the count of greens divided by the total number of colours in the matrix

So if there are a total of 10 green, red and yellow values, 5 of which are green then we should get 5/10 = 50%

And this part technically works for the measure that's been created, however, it does so for the whole month that's been selected in a slicer, whereas the heatmap is only for a week. (averaged)

So if we only select one week in the slicer, we get a correct result, but more than that it's wrong.

So we need to summarize or average per week somewhere, which is where I get lost

[AlexisOlson]

I think I might understand. It sounds like your percent green measure is using week number, which is a number 1-53 whereas the matrix visual is using WeekDayName Mon-Sun. To get the percentage you want, use the same dimensional granularity for the percentage measure as you have on your matrix visual. That is, use WeekDayName for both.

SUMMARIZE ( TableCount, Dim_Date[WeekDayName], Dim_Day[Hour] )

[thne123]

@AlexisOlson 

Thanks!

Sadly this didn't give me the expected result, I got the same result I got when I used the weeknumber column actually 

When I den count the number of greens I get 124 (it used to be 240). But the expected/correct outcome should be 52.

How I count the green rows:

var _tbl = SUMMARIZE(
                    TableCount,
                    [WeekDayLong],
                    [Hour],
                    "_Colour", [ColourMeasure]
                    )
return

COUNTROWS(FILTER(_tbl, [_Colour] = "Green"))

[AlexisOlson]

Unfortunately, I don't think I can debug any further without seeing your actual file.

If you return TOCSV ( _tbl ), do the results match the granularity and values of your matrix?

[thne123]

@AlexisOlson 

Yeah I understand, 

I tried 

var tbl = SUMMARIZE(
                    TableCount,
                    [WeekDayLong],
                    [Hour],
                    "_Colour", [ColorMeasure]
                    )
return
TOCSV(tbl)

and it returned the following, which looks weird to me

And with just one week selected in the date slicer it also looks weird,

But if I instead return:

COUNTROWS(FILTER(tbl, [_Colour] = "Green"))

it counts the correct greens rows (with just one week selected, it's when I use more weeks it get's weird).

Which is strange since it looks wrong in the TOCSV context.

Correct greens for one week should be 59, and 131 total

Edit: Maybe a countrows is wrong now that we summarize per week instead. 

Instead I should probably use SUM?

[AlexisOlson]

Those table previews seem OK to me at first glance (they only load 10 rows by default but you can use the optional arguments of the TOCSV function to load more). I can't tell what's going wrong.

[thne123]

Ah, I exported all the rows just to check in excel quickly. There are 131 rows, which is perfect. BUT, 124 of them are considered green here, which is incorrect when only 52 should be considered green. (verified that 52 is the correct number, and that the measure that colours them green, red etc is also correct)

So for:

var tbl = SUMMARIZE(
                    TableCount,
                    [WeekDayLong],
                    [Hour],
                    "_Colour", [ColorMeasure]
                    )
return

 This returns 131, which is correct:

COUNTROWS(tbl)

This returns 124, which is incorrect (52 expected)

COUNTROWS(FILTER(tbl, [_Colour] = "Green"))

Which makes no sense, because when I use:

   DIVIDE(
           COUNTROWS(FILTER(tbl, [Color] = "Green")),
           COUNTROWS(tbl)
           )

I get 43%

and 124/131 isn't 43%... 😄