3 Replies Latest reply on Nov 21, 2018 7:18 PM by mursid rahman

How to add multiple calculated measures

I need to create 3 separate calculated measures and add them together but am having difficulty.

I'm trying to calculate Active IDs + New IDs to show the total no of IDs.

First I need to filter on the valid IDs.

PROBLEM 1 - Calculating Active IDs:

I am currently using the following to calculate the Total no. of records in a view :

WINDOW_SUM(SUM([Number of Records]))

but using a filter (e.g. see below) and would like to create a calculated measure that will provide this same total number without needing to filter:

e.g I am filtering on ID using a Wildcard to Exclude Matched values that 'Does not start with' X

then using WINDOW_SUM(SUM([Number of Records])) to calculate the total no. of records with this filter in place. What formula can I use to calculate this instead?

PROBLEM 2 - Calculate Expiring IDs:

Similarly, I need to then create a separate calculated field for a separate calculation as follows :

e.g I am filtering on ID using a Wildcard to Exclude Matched values that 'Does not start with' X

Plus another filter on Year of Exit Date

then using WINDOW_SUM(SUM([Number of Records])) to calculate the total no. of records with this filter in place.

PROBLEM 3 - Calculating New IDs:

Similarly, I need to then create a separate calculated field for a separate calculation as follows :

e.g.

I am filtering on ID using a Wildcard on Matched values that 'Starts with' X

Plus another filter on Year of Targte Start Date

then I'm using WINDOW_SUM(SUM([Number of Records])) to calculate the total no. of records with both filters in place.

PROBLEM 4 - :

Now I need to add all of the calculated fields together tog display the no. of total net IDs:

[Active IDs] - [Expiring IDs] + [New IDs] = [Total No. of Net IDs]

I presume I need to create a separate calculated field to do that as well.

I'd appreciate any help and advice to create the 4 calculated fields as currently I'm having to do this manually to get the total and I've struggled for too long trying to work this out!

Thank you.

• 1. Re: How to add multiple calculated measures

Hi Simone,

Could you provide the packaged workbook for reference?

Try this while at it

SUM(IF STARTSWITH([ID], 'X') THEN 1 END)

This will get you a measure for New ID

Regards,

M.R

• 2. Re: How to add multiple calculated measures

Hi Simone,

Try these calculations. Hope this helps.

Active ID

SUM(IF NOT CONTAINS([ID], 'X') THEN 1 END)

Expiring ID

SUM(IF NOT CONTAINS([ID], 'X') AND DATEPART('year', [Year of Target Start Date]) = 2018 THEN 1 END)

New ID

SUM(IF STARTSWITH([ID], 'X') THEN 1 END)

Regards,

M.R

• 3. Re: How to add multiple calculated measures

Apologize. Correction on the NEW ID calculation.

New ID

SUM(IF STARTSWITH([ID], 'X') AND DATEPART('year', [Year of Target Start Date]) = 2018 THEN 1 END)