Filter one side of pivot table

[ndeeleysww]

Hi all,

Think this is not achievable, but I need to filter just one side of a pivot table.

I have a count of equipment by work group. This count never changes as its the total of the equipment. Joined to that is a count of work orders on that equipment, filerable by cost centre. I can create the table, but when I add in the filter it of course filters the whole table, and reduces the equipment count accordingly.

Any ideas how I can over come this one?

Thanks all

Neil

[sunny_talwar]

If I understand you question properly, I think you can use this {1} within your expression for total of the equipment for it to be not filtered by any selection.

I hope this will help.

Best,

Sunny

[ndeeleysww]

Brilliant - thank you. Do you know where I use the bit in the expression?

count({$ <[Equipment Type]-={" "}>} [Equipment Type])

[sunny_talwar · Accepted solution]

Replace $ with 1.

=Count({1<[Equipment Type]-={" "}>} [Equipment Type])

Note: Now your expression won't change to any selections you make. If that is not what you want, there may be other ways to achieve what you are looking for. You just need to clarify your requirement.

HTH

Best,

Sunny

[ndeeleysww]

Fantastic! Thanks for your help Sunny.

[sunny_talwar]

No Problem Just curious to know if that did exactly what you were looking to do?

[ndeeleysww]

Yes - the equipment count remains the same, but the equipment in relation to the filter changes. It does reorder the columns in the pivot table tho, which is a pain.

[sunny_talwar]

Reorder the columns? Really? I don't think it should do that. Can you show a screenshot of with and without selection and may be I can help you fix that as well.

Best,

Sunny

[ndeeleysww]

It's because I have a sort Match on the columns to put them in the required order:

Match([Safety and Legislative],

'MCERT',

'Fixed Gas Monitors',

'Crypto',

'Chlorination and Sulphination',

'Inlet Protection',

'Fixed Generators',

'Mobile Generators'

).

As soon as I choose a filter the match is removed:

Thanks for all your help Sunny!

[sunny_talwar]

Try using this as your sort expression:

Match(Only({1}[Safety and Legislative]),

'MCERT',

'Fixed Gas Monitors',

'Crypto',

'Chlorination and Sulphination',

'Inlet Protection',

'Fixed Generators',

'Mobile Generators'

).