3 Replies Latest reply on Aug 21, 2018 1:37 PM by Okechukwu Ossai

# FIXED then IF/EXCLUDE by dimension and measure

Hello! My first post. Hopefully, I've attached my sample packaged workbook correctly.

I need to determine:

• Total distinct client count and total units.
• Total distinct client count and total units by service.

Criteria:

• Exclude from counts those clients with less than 0.50 total units for Service 1 .
• Exclude from counts those clients with less than 0.50 total units for Service 2.
• Include clients regardless of total units for Services 3 & 4.

See client Y026 as an example of a client that should be included in total count & service 3 & 4 counts, but not service 2 counts. Client D079 should be excluded entirely.

Current Result with no filters or excludes

Service 1 clients: 3 units: 4.75

Service 2 clients: 5 units: 10.75

Service 3 clients: 5 units: 50.35

Service 4 clients: 4 units: 22.5

Distinct Count: 13

Total Units: 88.35

Desired Result

Service 1 clients: 2 units: 4.5

Service 2 clients: 4 units: 10.5

Service 3 clients: 5 units: 50.35

Service 4 clients: 4 units: 22.5

Distinct Count: 12

Total Units: 87.85

I created a simple exclude/include calculated field as a test for Service1.  This approach is not working because the units are not summed first. I believe I need to utilize LOD {FIXED [Client Id]: SUM([Units])} to obtain the total units for the client ID first. Then, filter or exclude based on the service option. I'm just not sure how to pull it all together. Thanks for your assistance!

• ###### 1. Re: FIXED then IF/EXCLUDE by dimension and measure

Hi Shan,

See solution in workbook attached.

Step 1: Create calculated field [Exclude]

IF [Service] = 'Service1' OR [Service] = 'Service2' THEN

IF {FIXED [Client ID], [Service]: SUM([Units])} < 0.50

AND {FIXED [Client ID]: SUM([Units])} < 0.50 THEN 'Exclude'

ELSE 'Include'

END

ELSE 'Include' END

Add this to the filter shelf and set to 'Include'.

Step 2: Create calculated field [Sum-Units]

{FIXED [Client ID], [Service Month], [Service]: SUM(

IF [Service] = 'Service1' OR [Service] = 'Service2' THEN

IF {FIXED [Client ID], [Service]: SUM([Units])} >= 0.50 THEN [Units] END

ELSE [Units] END)}

Step 3: Create calculated field [# of Clients]

IF {FIXED [Client ID], [Service]: COUNTD(IF [Sum-Units] > 0 THEN [Client ID] END)} > 0 THEN 1 END

Hope this helps.

Ossai

1 of 1 people found this helpful
• ###### 2. Re: FIXED then IF/EXCLUDE by dimension and measure

This solution worked for me! Thank you very much. Shan

• ###### 3. Re: FIXED then IF/EXCLUDE by dimension and measure

You're welcome.