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]

I am gonna admit to only skimming this one, if it required more, yell at me.

Calculated columns are always calculated exactly 1 time... ever. When the data is refreshed.
If you want to respond to filters/slicers, you must write a calculated field (aka measure).

 

[RunningInBoulder]

Thanks a lot for your answer.

Sorry I wasn't clear. My two calculated fields [Inventory] and [TestInventory] are measures, not calculated columns.

 

[scottsen]

Ya know. You were totally clear, and I was just too sleepy to read. Only 1 cup of coffee this morning, and um... more than 1 glass of wine last night Then, I was just about to reply w/ a calculate vs if() ... but started to feel guilty for not reading... again

Any chance you can post a workbook for me to play with? I will get this sorted out for you.

 

[RunningInBoulder]

Thank you so much.
Here is the file: http://www.filedropper.com/exampleissue

The only difference with the example above is that I added another small Dimension table called "TableProducts" to list the brand for each item.
The pivot table on the first sheet is already filtered to show the wrong results for the [TestInventory] measure.

 

[scottsen]

wow. I don't even know what to say, other than "ya, that is TOTALLY FREAKING WEIRD". I have to call in reinforcements, it certainly looks like we are hitting a bug here, but it is so​ basic, that is hard to believe.

 

[RunningInBoulder]

LOL... I was hoping to hear that my little pee wee brain had missed a simple step again.

 

[Tianbas]

I copied your example tables and formulas in a new file and its working as expected. I see the correct values for testinventory if the table is filtered or not. The countrows formula shows 0 for the grand total as well but that seems ok, because the first part of the if statement is not true for the total. For each row the result is ok.

Iam still using PP2010 so if its a bug then it came with the latest version

 

[scottsen]

The "starting over" theory is... not bad!

I was testing with 2010 as well, and this measure threw fits:
=IF( [Total Inventory] < 50, [Total Inventory], [Total Inventory] )

If I sliced on Cali and Color it was fine, but as soon as I added Florida... Colorado would break. It was incredibly weird.

 

[RunningInBoulder]

Thank you so much for your help guys.

I am also using PP2010. I downloaded the add-in a month ago.
I will delete my current version and re-download it from the website. Hopefullly this will solve the issue.