Ignoring Selection in Pivot Table

[Anonymous]

Hello, everyone! I have a simple pivot table that includes parent account and account as dimensions and then displays a number of numerical expressions. If there are no selections/filters, the pivot table shows all parent accounts and then all individual accounts nested underneath each parent, followed by the values. However, if a user filters on a particular account, instead of limiting the pivot table to a single row with that account, I'd like the pivot table to limit on the parent account dimension instead so that the user can see each of the related accounts rolling up to the same parent as the one selected.  Is this possible?  I cannot use parent account as the filter because there are other objects on the sheet that would be adversely affected.  Thanks in advance for your help!

Here's an example:

If the user filters on Account 1, I want the pivot to show all the values for its Parent Account (1-4) and not just limit the pivot to the row for Account 1.

[hector_munoz · Accepted solution]

Hi Ryan,

With a variable like:

vdParentAccountsAvailables ='|' & Concat([Parent Account], '|') & '|'

, and an expression like:

Sum({1} If(SubStringCount(vdParentAccountsAvailables, [Parent Account]) > 0, Sales))

, you should get what you need... I attach you a sample. Hope it helps you!

Regards,

H

[Anonymous]

Hector,

Thank you so much for this very clever solution!  I modified the expression as follows:

=if(count({1} If(SubStringCount(vdParentAccountsAvailables, [Parent Account) > 0, Account)) > 0,count({1<Stage={'*'}-{'Rejected*'}>}[Proposal]),0)

This way I could still use set analysis to form the expression for any of the dimensions that met the "count" condition you provided using the variable.

Cheers!

Ryan

[hector_munoz]

Very well solved, Ryan! Congrats!