Forum Discussion
Helper IWriting Measure that Returns Customer Specific Revenue based on Customer Specific Multiplier
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.
4 Replies
- amitchandak
Super User
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,
)))
npomboHi 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!
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.
