7 Replies Latest reply: Dec 1, 2016 10:00 AM by nayan lalla

# Set Analysis showing all values but Dimension to only show selection

Hi

I have an expression in a pivot table showing all values, no matter the "brand" selection.  However if I do make a selection, I want only the field selected to show, however still keeping the total value.

Attached herewith is a sample of a Sales QV Model and related data in excel format.

In this model, I have sales of brands through 3 different channels.  Each branch is part of a category.

So in my model, when i select "apple" , the pivot table should only show "apple" and not all other brands.

Please note the total sales should be 33923 and if i select fruit under category and it changes to 14190, that is still fine.

Kind regards

Nayan

• ###### Re: Set Analysis showing all values but Dimension to only show selection

Hi Plz check the attachment once,

In expression i have changed it , now its working based on selection.

Plz check once if it is giving results as you expected its k, if it is not i am too wait to get the solution.

• ###### Re: Set Analysis showing all values but Dimension to only show selection

Thank you for the reply Umamaheswar

The solution is almost there.  If apple is selected, it must still show total of 33,923.  Then when I select "Fruit" under the category, then it must give a total of 14,190.

Can this be done.

Kind regards

Nayan

• ###### Re: Set Analysis showing all values but Dimension to only show selection

Is this the goal?

Expression:

Sum({\$<Brand=>} TOTAL<Channel,Category> Aggr(DISTINCT Sum({\$<Brand=>}(Sales)),Channel,Category)) * Avg(TOTAL <Brand>1)

• ###### Re: Set Analysis showing all values but Dimension to only show selection

Hi Sunny T

Yes, that is correct.  Thank you.

Kind regards

Nayan

• ###### Re: Set Analysis showing all values but Dimension to only show selection

Hi Sunny T

If i had to apply alternate states to the expression, where would i put {State1*\$} in the expression.  I've tried, however, I'm unsuccessful.

Kind regards

Nayan

• ###### Re: Set Analysis showing all values but Dimension to only show selection

May be this:

Sum({State1*\$<Brand=>} TOTAL<Channel,Category> Aggr(DISTINCT Sum({State1*\$}{\$<Brand=>}(Sales)),Channel,Category)) * Avg({State1*\$}TOTAL <Brand>1)

• ###### Re: Set Analysis showing all values but Dimension to only show selection

Thank you again.  Much appreciated .