Problem with Calculated Fields that contain IF and SWITCH statements

[RunningInBoulder]

Hi everyone,

I am new to powerpivot and I cannot solve a simple problem with IF statements / SWITCH formulas in my calculated fields.
The calculated fields return the correct results when I do not filter the data in my pivot table. But as soon as I filter something, I get wrong/blank results

I would like your help to understand WHY my logic is flawed as I am trying to replicate the same type of formula for many different scenarios.

Enough rambling, here is my problem:

Simplified version of my model:
- I have a data table with three columns (inventory per item per state).</SPAN>
[TABLE="width: 292"]
<TBODY>[TR]
[TD]State</SPAN>
[/TD]
[TD]Product</SPAN>
[/TD]
[TD]InventoryInCases</SPAN>
[/TD]
[/TR]
[TR]
[TD]California</SPAN>
[/TD]
[TD]Item A</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[/TR]
[TR]
[TD]New York</SPAN>
[/TD]
[TD]Item A</SPAN>
[/TD]
[TD]20</SPAN>
[/TD]
[/TR]
[TR]
[TD]Illinois</SPAN>
[/TD]
[TD]Item A</SPAN>
[/TD]
[TD]30</SPAN>
[/TD]
[/TR]
[TR]
[TD]Florida</SPAN>
[/TD]
[TD]Item A</SPAN>
[/TD]
[TD]40</SPAN>
[/TD]
[/TR]
[TR]
[TD]Texas</SPAN>
[/TD]
[TD]Item B</SPAN>
[/TD]
[TD]25</SPAN>
[/TD]
[/TR]
[TR]
[TD]Colorado</SPAN>
[/TD]
[TD]Item B</SPAN>
[/TD]
[TD]60</SPAN>
[/TD]
[/TR]
[TR]
[TD]New York</SPAN>
[/TD]
[TD]Item B</SPAN>
[/TD]
[TD]35</SPAN>
[/TD]
[/TR]
[TR]
[TD]Illinois</SPAN>
[/TD]
[TD]Item B</SPAN>
[/TD]
[TD]25</SPAN>
[/TD]
[/TR]
[TR]
[TD]Washington</SPAN>
[/TD]
[TD]Item B</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


- I have a dimension column with unique states (my Key) and corresponding regions. </SPAN>
[TABLE="width: 175"]
<TBODY>[TR]
[TD]State</SPAN>
[/TD]
[TD]Region</SPAN>
[/TD]
[/TR]
[TR]
[TD]Illinois</SPAN>
[/TD]
[TD]Central</SPAN>
[/TD]
[/TR]
[TR]
[TD]Texas</SPAN>
[/TD]
[TD]Central</SPAN>
[/TD]
[/TR]
[TR]
[TD]Colorado</SPAN>
[/TD]
[TD]Central</SPAN>
[/TD]
[/TR]
[TR]
[TD]New York</SPAN>
[/TD]
[TD]Northeast</SPAN>
[/TD]
[/TR]
[TR]
[TD]Massachusetts</SPAN>
[/TD]
[TD]Northeast</SPAN>
[/TD]
[/TR]
[TR]
[TD]Florida</SPAN>
[/TD]
[TD]Southeast</SPAN>
[/TD]
[/TR]
[TR]
[TD]California</SPAN>
[/TD]
[TD]West</SPAN>
[/TD]
[/TR]
[TR]
[TD]Washington</SPAN>
[/TD]
[TD]West</SPAN>
[/TD]
[/TR]
[TR]
[TD]Oregon</SPAN>
[/TD]
[TD]West</SPAN>
[/TD]
[/TR]
[TR]
[TD]Wyoming</SPAN>
[/TD]
[TD]West</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

- Relationship is defined as Table "DataTable" ; Column "State"
Related Lookup Table "DimTable" ; Related Lookup Column : "State"


I created a pivot table with my states as rows ("States" from the Dim table this time... I know... made that mistake before...).

In the values, I have two calculated fields:
- [Inventory] is simply the sum of the inventory in my data table = SUM(TableInventory[InventoryInCases])

And here is my problem... my second calculated field:
- [TestInventory] = IF([Inventory] < x, [Inventory]+1, [Inventory]-1)
This will produce the right result when the states (rows) in my pivot table are not filtered.

--> However When I filter the rows (again, states from Dim table), the value of [Inventory] in the THEN and ELSE part of the if statement becomes 0 and the calculated result in my pivot becomes 1 and -1.

For instance:</SPAN>
[TABLE="width: 192"]
<TBODY>[TR]
[TD]Row Labels</SPAN>
[/TD]
[TD]Sum of InventoryInCases</SPAN>
[/TD]
[TD]Inventory</SPAN>
[/TD]
[TD]TestInventory</SPAN>
[/TD]
[/TR]
[TR]
[TD]Colorado</SPAN>
[/TD]
[TD]60</SPAN>
[/TD]
[TD]60</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[/TR]
[TR]
[TD]Florida</SPAN>
[/TD]
[TD]40</SPAN>
[/TD]
[TD]40</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]Illinois</SPAN>
[/TD]
[TD]55</SPAN>
[/TD]
[TD]55</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[/TR]
[TR]
[TD]New York</SPAN>
[/TD]
[TD]55</SPAN>
[/TD]
[TD]55</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[/TR]
[TR]
[TD]Texas</SPAN>
[/TD]
[TD]25</SPAN>
[/TD]
[TD]25</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]Grand Total</SPAN>
[/TD]
[TD]235</SPAN>
[/TD]
[TD]235</SPAN>
[/TD]
[TD]234</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


FYI I get the same error when I add IF(COUTNROWS(VALUES()) as follows:</SPAN>
=IF(COUNTROWS(VALUES(TableStates[State]))=1,IF([Inventory]<50,[Inventory]+1,[Inventory]-1),0)</SPAN>

The Grand Total just becomes 0...


This means that the value of [Inventory] seems to be evaluated correctly in the first part of the IF statement but becomes 0 in the calculation of the THEN and ELSE statement.

This error does not occur when I use the states from my data table as rows in my pivot table.
The relationship between the tables seems fine though (straight many to one).

You will probably tell me to use CALCULATE instead of IF in my calculated field but in my real model I am attempting to compare the values of multiple calculated fields together with a SWITCH formula to then produce different calculations based on the result.
I therefore need your help to understand why my logic is flawed and what approach I should take to compare the values of calculated fields and perform different calculations based on the results.

I have been stuck on this for a week and I cannot find a thread that explains what I am doing wrong.

Thank you very much in advance!</SPAN>

 

[scottsen]

For the record, by start over *I* meant create a new workbook, rather than working with the existing workbook. Re-installing the addin is not a horrible idea either, but ... I think Tianbas and I feel your workbook took drugs.

 

[scottsen]

Talked with Rob (powerpivotpro) who said "uh... ya. That looks like a bug alright". I really want to believe recreating the workbook will magically fix this.

 

[XLBob]

I am using Excel 2013, it's working ok on my PC.

 

[RunningInBoulder]

All, I recreated my example above on my wife's computer and it works fine.
Another Excel add-in on my work computer must be causing the issue.

P.S: Scott, you talked to Rob?! The... Mr... Rob... about... about... my problem?
I'M NOT WORTHY !!! I'M NOT WORTHY!! Thankyouthankyouthankyou !! I'm going to shed a lil' tear right now.

 

[HillDragon]

Hey, I was just playing with this and the issue is coming from the the Products slicer; Item B in particular.

When you slice by product you are adding a second line of States when SUDDENLY Inventory is now > AND < 50.

Bam, wonky stuffs! Now if I could just think of a way to use in this in the future...