How to hide partial sum if dimension value is null?

[#1]

Hi,

I have a pivot table with three dimensions (group, sub group and product). All products are part of a group, but not all products are part of a sub group! In my pivot table I want to show partial sums for sub groups, but only for sub groups that are not null. If I check the 'suppress when value is null' option it suppresses the products that are not part of a sub group. But I want to show them as well. How do I fix this issue?

[Gysbert_Wassenaar]

Can you post an example document with some data?

[#3]

Hi

Select that dimension and check the supress null value option in dimension tab

[#4]

If I do this then products that do not have a sub group will be supressed as well (products 1,2 and 5 in screenshot)

[jonathandienst · Accepted solution]

Hi

It looks like you are using Indent mode for your pivot.I may be mistaken but it may not be possible to do what you want to do in indent mode.

What you can try is to modify your expression to use Dimensionality() to override the partial sum when the sub group is null, something like:

=If(Dimensionality() = 2 And Len([Sub Group] = 0, 0, ... current column expression ....)

I think the subtotal is at Dimensionality 2 but you may have to tweak this to work in your table.

And, of course, enable suppress zero values. If it does not work, try turning off indent mode.

HTH

Jonathan

[#6]

Thank you very much! This works perfectly. (with an extra ')' after [Sub Group] 😉 )

It worked using indent mode, so no need to turn it off.