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:
use sum function to get the total count for single or multi.
=Sum([Single variable])
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
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
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.
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)
That worked perfectly. Thank you so much!