Pivot table expressions not working as they should...

[dapostolopoylos]

Hello everyone, here are the facts:

I have created a pivot table that looks like this:

As you can see, this pivot shows quantities, values, prices and variation for every material group per year.

Quantities and values are ordinary fields loaded directly from the load script.

Price is calculated on the pivot with an expression that does column reference like this:

=[Amount EUR]/[Quantity (GR)]

In order to calculate the variance for volumes i use this expression, also with column reference:

=([Quantity (GR)]-before([Quantity (GR)]))*[EUR Price of ALL entries]

For the price variance column i use this expression:

=([EUR Price of ALL entries]-before([EUR Price of ALL entries]))*before([Quantity (GR)])

As you can see from the screenshot the variances columns aren't absolutely right and i cannot understand why.

Any help will be really appreciated!

[sunny_talwar]

Since your chart starts from 2015A, there is nothing to look using Before function..... You might realize that in 2016A, the data for variance is populating because 2015A is the before and data is available

What did you expect to see for 2015A? May be you do have 2014A data, but not showing it in the chart? You can still do it, if this is true... but we would need to know the expressions used for Amount EUR and Quantity (GR) and also let us know if you are using a calculated dimensions

[dapostolopoylos]

No i am not using a calculated dimension.

For 2015A i don't expect to see results as it is indeed the first year in my data. I tried to hide the variance columns for 2015A but i haven't found a way yet, if you know one please share...

The problem is showing in the 2016A section of the pivot.

If you make the calculations by yourself you'll see that the Price column is calculated correctly but the variances don't.

The values presented in the chart are pretty close to the correct but they are not correct...

[sunny_talwar]

You want to hide 2015A completely? Not even Quantity and amount should hide without selections? Also, you will have to move away from column labels in your variance expression and use the actual expressions with set analysis to ignore selection in your year field and then use * Avg(1) to remove all out of selection columns.

I think this might be easier to show if you are able to share a sample here.

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

[dapostolopoylos]

Ok, i am sending you stalwar1 the sample, thank you in advance

[sunny_talwar]

Check the attached out... may need to modify based on your requirement, but you get the idea

[dapostolopoylos]

Unfortunately it didn't work...

My main problem, the variances, is still unsolved...

All i wanna do is to make a simple calculation horizontally like i would do in Excel...

[dapostolopoylos · Accepted solution]

I figured it out, i am not proud of myself and the time i lost on this but i feel relieved...

On the pricing calculation, the division between value and quantity, too many decimals where generated that weren't displayed on the chart but they were messing up the results on the variance calculations...

I just used Round(Price,0.01) and everything worked fine...

[sunny_talwar]

Great