Counting Customers Based On Calculated Measure

[Former Member]

My goal is to define customers as single property or multi property based on number of collateral pieces. I created a [collateral count] measure. Where I'm stuck is counting how many of my customers have [collateral count]>1 and how many have [collateral count]=1.

My table looks like this

Customer Collateral Count

A 1

B 1

C 2

I need my result to be

Property Type Customer Count

Single 2

Multi 1

I've beet able to create a measure that indicates single or multi, but have not been able to count these items.

Any help would be appreciated!

Jay

What I have so far:

[amitrathi239 · Accepted solution]

use sum function to get the total count for single or multi.

=Sum([Single variable])

[Former Member · Accepted solution]

Amit,

Thanks for the response, unfortunately this hasn't worked.

This is the logic behind single and multi:

Single: =Count([Customer Name]) Where ([Collateral Count]=1)

Multi: =Count([Customer Name]) Where ([Collateral Count]>1)

I tried to take the sum(Single) and it populated null, and when I tried to take the sum(Multi) I got a number in the thousands when it should be closer to 50 based on the data set.

Any ideas?

I've also tried to use =if([collateral count]>1;"Multi";"Single") and have been able to populate customers with the correct property indicator, and then used count([customer]) where ([measure]="Multi") but haven't had luck there either.

This is what is populating based on adding the Sum([single]) and Sum([Multi])

Thanks,

Jay

[amitrathi239 · Accepted solution]

After doing measure=if([collateral count]>1;"Multi";"Single") try this.

=sum(if([measure]="Multi") then 1 else 0)

if it will work then similar for single

[Former Member · Accepted solution]

I think we're close, but still not working. The =if([collateral count]>1;"Multi";"Single") equation is identifying single v. multi, and the sum(=sum(if([measure]="Multi") then 1 else 0) is marking 1 for the correct items, but when I remove the customer dimension to aggregate by servicing user, the dimension gets ignored and it aggregates all of the collateral under each user and marks both users as having only 1 multi-property v identifying they each are attached to multiple customers each having a different measure.

[amitrathi239 · Accepted solution]

you have to use Customer object in your formula with foreach context.

try like this

=sum(if([measure] foreach ([customer])="multi") then 1 else 0)

[Former Member · Accepted solution]

That worked perfectly. Thank you so much!