Getting #NUM! for outer nests of IF statement division in PowerPivot measure

Question

I have some sales figures for which I have values in pounds, units, and dollars. From PowerPivot, I'm creating a pivot with Periods columns (Current Week, MTD, QTD, YTD) and sub-columns of "Measure" (Dollars, Pounds, Units). The calculation I'm using gets the percent difference between this year's value for the given Period and Measure, and last year's value for the same Period and Measure, e.g., ([MTD This Year]-[MTD Last Year])/[MTD Last Year].

To do this, I've added in PowerPivot next to my Data table, a one-column Period table and a one-column Measure table with no relationships created to Data. I use these for my column values, and use nested IF statements to determine what to do for which Periods and Measures.

The measures I use to get this year's and last year's values are running just fine. But when I do the division, I'm getting #NUM! errors only for the outer nests of the IF statement. I've played around with this and simplified the formula to it's essential parts (i.e., this is a simplification of my formula):

=
(
IF(
    COUNTROWS(VALUES(Periods[Period]))=1,
    IF(
        VALUES(Periods[Period])="Current Week",
        1,
        IF(
            VALUES(Periods[Period])="MTD",
            2,
            IF(
                VALUES(Periods[Period])="QTD",
                3,
                IF(
                    VALUES(Periods[Period])="YTD",
                    4
                )
            )
        )
    ),
    0

)
) 
/
(
IF(
    COUNTROWS(VALUES(Periods[Period]))=1,
    IF(
        VALUES(Periods[Period])="Current Week",
        1,
        IF(
            VALUES(Periods[Period])="MTD",
            2,
            IF(
                VALUES(Periods[Period])="QTD",
                3,
                IF(
                    VALUES(Periods[Period])="YTD",
                    4
                )
            )
        )
    ),
    0
)
)

I expect this to return "1" for all values. Instead, it returns #NUM! for all Current Week and MTD values and "1" for the rest. If I rearrange the nested arguments, #NUM! is always returned for the outer arguments.

Any idea what's going on here?

Frameworker247 Frameworker247 · Accepted Answer · 2014-03-14T16:38:41

I figured out a solution, although I'm not clear on why this works while while the other structure didn't. Here's how I set it up:

=
IF(
    COUNTROWS(VALUES(Periods[Period]))=1,
    IF(
        VALUES(Periods[Period])="YTD",
        DIVIDE(1,1),
        IF(
            VALUES(Periods[Period])="QTD",
            DIVIDE(2,2),
            IF(
                VALUES(Periods[Period])="MTD",
                DIVIDE(3,3),
                IF(
                    VALUES(Periods[Period])="Current Week",
                    DIVIDE(4,4)
                )
            )
        )
    ),
    0
)

My takeaway is to avoid dividing two nested IF statements against each other.

Getting #NUM! for outer nests of IF statement division in PowerPivot measure

1 Answers