How to force null value into pivot table line

[#1]

Hi,

I'd like to ask a little help.

I have a pivot chart like this one below:

Product	MeasureA	MeasureB
A Product	2	3
B Product	3	5
C Product	4	6
Total	9	-

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

[sunny_talwar]

Try using the dimensionality() function for this:

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

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

[sunny_talwar]

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

[sunny_talwar]

This?

[#5]

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

[#6]

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

[sunny_talwar]

Try this expression:

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

[#8]

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?

[sunny_talwar · Accepted solution]

Try this expression:

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

Output:

[#10]

Perfect! Thank you!