IF NAME IS X,Y,Y, then..

[setis]

Hi experts, I need some help with a couple of measures. I hope that you can help me:

CustomerA Fee = CALCULATE([TotalAmount];Accounts_Lookup[report]="Fixed fees";Customers[Name]="CustomerA")

This measure is working fine and it gives me the Fixed fees for Customer A.

#Cases Customers ABC = CALCULATE([CountCases];
FILTER(Customers; Customers[Name]="Customer A"|| 
Customers[Name]="Customer B"|| 
Customers[Name]="Customer C")) 

This measure works fine too and it gives me the nr of cases for customers A, B and C. What I need is to allocate the Fixed fees of Customer A among Customers A, B and C in this way:

(Customer A fee)/Cases Customers ABC) * Nr of cases of the selected customer if this is A, B or C.

What I'm trying is:

Customer A Allocation = IF(
SELECTEDVALUE( 
Customers[Name]="CustomerA"|| 
Customers[Name]="CustomerB"|| 
Customers[Name]="CustomerC"); 
([CustomerA Fee]/[#Cases Customers ABC])* 
CALCULATE([CountCases]; 
FILTER(Customers;Customers[Name]=SELECTEDVALUE(Customers[Name])));[Total revenue]) 

But this is obviously not working. Thank you very much in advance

[AlB]

Hi @setis 

Where/how are you using those measures? In card visuals? In a table or matrix visual? Is there a filter context applied on them? Which one?

Before looking further, the first parameter of the IF in the last measure looks weird. I think what you mean is this:

Customer A Allocation =
IF (
    SELECTEDVALUE ( Customers[Name] ) IN { "CustomerA"; "CustomerB""CustomerC" };
    ( [CustomerA Fee] / [#Cases Customers ABC] )
        * CALCULATE (
            [CountCases];
            FILTER ( Customers; Customers[Name] = SELECTEDVALUE ( Customers[Name] ) )
        );
    [Total revenue]
)

 where I have only modified the part in red.

[setis]

Dear @AlB ,

Thank you for looking into this. 

I need to use this on a Matrix visual and there can be some filters applied on the type of cases or type of customer.

Your proposed correction does not display an error but the amount of CustomerA that needs to be distributed among Customers A, B and C now on the three customers .

[setis]

Update: 

OK I can see that the mistake is probably on the measure:

#Cases Customers ABC = CALCULATE([CountCases];
FILTER(Customers; Customers[Name]="Customer A"|| 
Customers[Name]="Customer B"|| 
Customers[Name]="Customer C")) 

This measure by itself gives me sum of cases for Customers A, B and C. However when I  put it in a matrix column with all the customers it shows me the nr of cases for the selected customer and not the sum of cases for the 3 of them -> (A+B+C).

How can I correct this measure so it shows for Customers A, B and C the result of (CountCases for CustomerA + CountCases for CustomerB + CountCases for CustomerC) in a Matrix ?

I guess that what I'm looking for is that if the sum of cases for Customers A, B and C is 45 it shows me in the matrix:

Customer A	45
Customer B	45
Customer C	45
Customer D
Customer E
Customer F

What I'm getting now is:

Customer A	20
Customer B	20
Customer C	5
Customer D
Customer E
Customer F

Thanks 🙂

[AlB · Accepted solution]

@setis 

If there's a filter context active on Customers, only the selected customer will be visible. I don't know what the code for the [CountCases] is but at least the second part of the CALCULATE needs to be modified like this:

#Cases Customers ABC = CALCULATE([CountCases];
FILTER(ALL(Customers); Customers[Name]="Customer A"|| 
Customers[Name]="Customer B"|| 
Customers[Name]="Customer C")) 

 and you can use the IN operator for simplicity:

#Cases Customers ABC = CALCULATE([CountCases];
FILTER(ALL(Customers); Customers[Name] IN {"Customer A";"Customer B";"Customer C"})) 

[setis]

@AlB this is exactly what I was missing. Thanks a lot!!