Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
Can you post an example document with some data?
Hi
Select that dimension and check the supress null value option in dimension tab
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)
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
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.