On a pivot table I am getting a "0" result in the total column, although there are values in the columns.
This only seems to be happening for values that are being calculated using If conditionals. As any other values are displaying correctly on the total column.
I would appreciate any support or tips that anybody might have.
thanks before hand. I am attaching a screenshot to display the row with the "0" total. Code being used for the expression where I am getting the zero result, is as follows:
Most likely something that is not contained within an aggregation, possibly the field MandantBereichCaptionShort or the variable vFormat, returns null in the context of a (sub)total because it has multiple possible values in that context (but only one possible value within the context of individual rows).
Thanks for the Info. Yes, the Mandatbereichcaptionshort are the defined parameters for the columns in the pivot which in some cases would return a null, depending on the selected filter. vFormat = 1, we use this to multiply the number to get a positive result.
But in this case, is there a way to have the total column display the total sum of the rows being displayed?
I also looked into changing the (Total Mode) to sum the rows, but its greyed out.
With Pivot Table, you can't use Total Mode - that's just for straight tables. You'll have to refactor the code so that it'll total correctly, or use If(Dimensionality()=0,FormulaForTotals,RegularFormula) to work around the problem (note that this will only work for totals, if you want it to work for subtotals, you'll need different dimensionality values depending on the size of your pivot table and where the subtotals are located).
