Replacing one measure with another with dynamic computations

Question

Use Case:

In the underwriting department, we have group accounts (Parent Companies) and multiple location stores linked to some group accounts.

1.      If the Cust Crdt Grp Nbr doesn’t have a Customer Group Credit Limit (or is equal to zero), then I must take the distinct summation value(s) of the “Cust Location Nbr”, to fill in that void.

2.      BUT if the “Cust Crdt Grp Nbr” DOES contain a value greater than zero AND  the “Cust Location Nbr” also contains a value greater than zero ; I must add both distinct values together to obtain the full credit line amount. 1.      (Example: Grp = $75.00, $10.00, $10.00, & Location = $5….Total Credit Line limit should be $100)

Goups and locations .xlsx

Sebastiaandb · Answer

@ccano Hi, i tried to understand what you want to achieve but it seems a bit hard for me to understand (probably  my fault ;-)).

From what i read you:

1- Want a distinct sum of Cust Location Credit Limit if there's no Cust Crdt Grp Nbr (and thus also no Customer Group Credit Limit).

2- Want a distinct sum of Cust Location Credit Limit + distinct sum of Customer Group Credit Limit if both Cust Location Nbr and Cust Crd Grp Nbr are available.

The problem with what i see in your dataset is that all of your Cust location Nbr's are unique. So there's no distinct Cust Location Credit limit to be made as they'r already unique for every location. Do you want to sum unique values based on a similar Cust Crd Grp Nbr?

Basically i don't really understand your example of 75,10,10 & location. If you can explain your example in more detail, i'm sure we can solve your riddle :-).

Greetings,

Seb