No fliter on pivot table. (pick and match definition)

[#1]

I have pivot table which has Dimension is Group and Expression is Date, cells are filled sum(Number).

If you click a number, this table will be filtered (shrink). I'm trying to keep the whole numbers without shrink.

I tried =sum({1}Number), but I couldn't resolve in this case.

Does anyone have idea?

- Each group has different formula

- I have to use this table layout (Dimension is Group and Expression is Date)

- Group is made from sum() of different status numbers.

Please see attachment.

[m_woolf]

Check the Read Only box on the General tab of the chart properties dialog.

[#3]

You can try 'alternate state' option. Kinda like making it an island(detached) so that irrespective of what selections you make anywhere in the application it wont affect the results displayed in the table.

[#4]

Thank you for your reply, Ajay.

I could verify the behavior on alternate state sample but I'm not sure it will work on actual project. I'll try them.

Thank you very much.

[#5]

I set 'Alternate State' for Number as 'test01' and used 'set analysis' live below. but it doesn't work.

=pick(

    match(

        [Group name],

        'AAA',

        'BBB',

        'CCC',

        'Avg'

    )+1,

    0,

    sum({test01}Number),

    sum({test01}Number),

    sum({test01}Number),

    sum({test01}Number)/count({test01}Number),

)

[#6]

Another simple way I can think of is make an island table with your required dimension and expression fields in the Data-model and make that table read only.

Attached with an example.

[#7]

Thank you quick response.

I can't use 'Read only' because actual project has a detail information list too. If you click a number on the table, the detail list is filtered and show up only related item to the clicked number.