Writing Measure that Returns Customer Specific Revenue based on Customer Specific Multiplier

[npombo]

Hello,

I am attempting to write a measure that examines the customer name before returning a customer specific revenue given a customer specific multiplier multiplied by the amount of items sold. For example, if the customer is customer 1 in this case, then we would take the customer 1 specific multiplier and multiply it by the number of items sold to get the customer 1 specific revenue (if customer = customer 1, then customer 1 multiplier * # of items sold = customer 1 revenue). However, the measure does need to be able to store multiple (up to 30) different customer specific multipliers and in turn know when to use each one based on filtering from a slicer that allows you to choose between customers.

I have written the following measure that only appears to work for customer 1 and not the following customers in this example. The numbers 5, 10, and 15 are placeholder customer specific multipliers.

Measure1 = SUMX (‘Table’, IF(‘Table’[Customer]= “Customer 1”, ‘Table’[Items Sold]*5, IF((‘Table’[Customer]= “Customer 2”, ‘Table’[Items Sold]*10, IF((‘Table’[Customer]= “Customer 3”, ‘Table’[Items Sold]*15 …..

Here is another measure I’ve attempted but have had no luck with:

Measure2 = SWITCH(

TRUE(),

‘Table’[Customer] = “Customer 1”, ‘Table’[Items Sold]*5,

‘Table’[Customer] = “Customer 2”, ‘Table’[Items Sold]*10,

‘Table’[Customer] = “Customer 3”, ‘Table’[Items Sold]*15,

))

Any feedback and help are much appreciated.

[v-jayw-msft]

Hi @npombo ,

I'd like to suggest you to create a Customer and Multiplier table like below:

Customer      Multiplier

Customer 1	5
Customer 2	10
Customer 3	15

Then create a relationship between fact table and new table and you could create measure like below to get the result.

Revenue = selectedvalue('Table'[Items Sold])*selectedvalue('Customer and Multiplier'[Multiplier])

Best Regards,

Jay

[npombo]

Hi Jay,

I unfortunately can't create new tables because I am working within the realms of a live Power BI dataset. Therefore, the analysis has to be solely derived from measures.

[amitchandak]

@npombo , Try one of the two

Measure2 = Sumx('Table', SWITCH(

TRUE(),

‘Table’[Customer] = “Customer 1”, ‘Table’[Items Sold]*5,

‘Table’[Customer] = “Customer 2”, ‘Table’[Items Sold]*10,

‘Table’[Customer] = “Customer 3”, ‘Table’[Items Sold]*15,

))

or

Measure2 = Sumx(values('Table'[Customer]), calculate (SWITCH(

TRUE(),

Max('Table'[Customer])= “Customer 1”, ‘Table’[Items Sold]*5,

Max('Table'[Customer])= “Customer 2”, ‘Table’[Items Sold]*10,

Max('Table'[Customer])= “Customer 3”, ‘Table’[Items Sold]*15,

)))

[npombo]

Hi @amitchandak,

Thank you for your suggestions. Unfortunately, neither of those proposed solutions seem to be doing the trick. I should note that the [Items Sold] field is a measure and is now storing itself in a different table, which may be why this still isn't working. Here is what I'm attempting below (which is still following your proposed code besides the table names):

Measure2 = Sumx('Table 1', SWITCH(

TRUE(),

‘Table 1’[Customer] = “Customer 1”, ‘Table 2’[Items Sold]*5,

‘Table 1’[Customer] = “Customer 2”, ‘Table 2’[Items Sold]*10,

‘Table 1’[Customer] = “Customer 3”, ‘Table 2’[Items Sold]*15,

))

or

Measure2 = Sumx(values('Table 1'[Customer]), calculate (SWITCH(

TRUE(),

Max('Table 1'[Customer])= “Customer 1”, ‘Table 2’[Items Sold]*5,

Max('Table 1'[Customer])= “Customer 2”, ‘Table 2’[Items Sold]*10,

Max('Table 1'[Customer])= “Customer 3”, ‘Table 2’[Items Sold]*15,

)))

The error I am getting for both when attempting to use a Card visual is that the Argument in SWITCH function is required.

Let me know if you have any other ideas. Thank you!