1 Reply Latest reply on Jun 26, 2018 7:56 AM by Mavis Liu

# Show only the Dimensions that have a given measure - but showing all the measures within Dimension

Hello All,

I am looking to see the % concentration of work within a department by each client.

How would I filter my clients to see only those clients that have 85% or more of their work in a given department?

But I want to see the % spread across each department for those clients - so I can't just filter it to be >85% as I still would like to see the %s below 85% for those clients.

For example:

 Client Name Dept 1 Dept 2 Dept 3 ABC 50% 40% 10% XYZ 90% 5% 5%

 Operating Montreal

I want to create a filter that would only show client XYZ rather than ABC but I want the filter to show me all departments for that client.

Anyone got any idea how I would create this?

Many thanks,

Darren

• ###### 1. Re: Show only the Dimensions that have a given measure - but showing all the measures within Dimension

Hi Darren,

Create an LOD calculation like:

{EXCLUDE [Department] : max(

{INCLUDE [Client Name], [Department] : SUM([Value])})}

This calculation works out the value per client and department, then it works out the maximum of those values per client across those departments.

E.g. This would be 90% for Client XYZ.

Put this field into filters and set to at least:

Thanks and please see attached workbook.

Mavis