Nested IF function not generating correct values when trying to return absolute values in Power Pivot. Help!

[bsm818]

Hello everyone! I work for a merchandising company, and I am working on generating a report that summarizes the total amount of product movement that has occured from an old shelf planogram to a new shelf planogram.

I am working with two fact tables (APOLLO XPOS 1 and 2) that reflect the most recent planogram information and the prior, and two dimension tables containing Planogram ID#'s (APOLLO VPOG ID) and item information (IM) as seen below;

Essentially, what I am trying to calculate is any changes in both shelf an item is placed on, and the X-postion of the item along the horizontal axis of each shelf.

I have created SUM measures to total both the previous and new values for LINEAR (which is how many inches and item is using), XPOS (the x-position of the item) and a MAX measure of shelf (to summarize how many shelves are in a planogram), along with a measure to calculate the footprint width of a planogram which can be used to derive linear footage (For example, a 3 foot planogram with 9 shelves would equate to 27 feet of liner space within a planogram). These are pictured below;

The logic I am trying to move forward with is to subtract PREVIOUS XPOS from NEW XPOS to calculate the change in XPOS, and if the result if a negative number, return the value as a positive (because you can't have a negative amount of change in space). It gets complicated here when dealing with items that have either been removed or added between planogram versions, which I utilized nested IF functions to return BLANK if there are blanks in either NEW or PREVIOUS XPOS columns;

This measure accounts for the blanks correctly, but it is incorrectly calculating items that have a NEW XPOS of "0" that have a PREVIOUS XPOS larger than "0";

These items essentially moved from a 31 and 26.26 inches along the X-axis of their respective shelves, to 0 (which equates to the beginning of the shelf), which should produce a change value of 31 and 26.26 respecitvely, but they are generating blanks.

Is there a problem being caused here by nesting too many IF functions in the measure? Is there a better measure to utilize to accomplish this calculation?


Thnaks to anyone who is able to help! I hope I was able to effectively explain what I am trying to accomplish here.

 

[rondeondo]

Hi BSM
it may be that the formula is interpreting a 0 as blank, it shouldn't but hey computers can do strange things. There's a handy think in excel, formula bar - evaluate formula that is useful for debugging, can highlight when the data is not being interpreted as expected.
An option for simplification for your formula:
if(and(isnumber([PREVIOUS XPOS]),isnumber(NEW XPOS)),ABS([NEW XPOS]-[PREVIOUS XPOS]),"")

The number of if's in your formula should be OK, I think there is a limit but it's more than that. The above will work in excel, may need some changes for power pivot, sorry haven't used power pivot.

Also, when I'm finding a formula not doing as I expect, I will do each part of it in it's own column, like a manual version of the evaluate formula function.