10 Replies Latest reply: Jul 24, 2015 1:34 PM by Sunny Talwar

# How to force null value into pivot table line

Hi,

I'd like to ask a little help.

I have a pivot chart like this one below:

ProductMeasureAMeasureB

A Product

23
B Product35
C Product46
Total9-

MeasureA and MeasureB are sum(<set analysis>Volume) expressions.

I have to show null value in MeasureB column for total.

Does somebody know a working solution for this? I tried with Product -= {''Total'} in set analysis but it shows zero value in the total cell, but I need null value.

Thank you

Gabor

• ###### Re: How to force null value into pivot table line

Try using the dimensionality() function for this:

If(Dimensionality() = 1, Null(), YourExpression)

If(Dimensionality() = 0, Null(), YourExpression)

• ###### Re: How to force null value into pivot table line

Is total a row from the database or is a total you calculate in the chart?

• ###### Re: How to force null value into pivot table line

From the database. All of the Production columns value has dimensionality 1.

• ###### Re: How to force null value into pivot table line

This?

• ###### Re: How to force null value into pivot table line

Total is an element from the Product. The dimensionality functions does not work in that case.

• ###### Re: How to force null value into pivot table line

Try this expression:

=If(Sum({<Product -= {'Total'}>}MeasureB) = 0, Null(), Sum({<Product -= {'Total'}>}MeasureB)).

• ###### Re: How to force null value into pivot table line

Thank you. The solution is closer, but it shows null instead of 0 if A Product = 0, but I need null only in the total row. Do you have an idea?

• ###### Re: How to force null value into pivot table line

Try this expression:

=If(Product = 'Total', Null(), Sum({<Product -= {'Total'}>}MeasureB))

Output:

• ###### Re: How to force null value into pivot table line

Perfect! Thank you!

• ###### Re: How to force null value into pivot table line

Awesome

I am glad it worked.

Best,

Sunny