create a calculated measure or column based on different conditions

[ljx0648]

Hi guys,

Below is the data I have:

Customer	Count of Plan Purchased	Type	Frequency	Description	Amount
A	1	Weekly	1	Pay every 1 week	10
B	2	Weekly	2	Pay every 2 week	10
C	3	Monthly	1	Pay every 1 month	10
D	4	Monthly	12	Pay every 12 month	10

My goal here is calculate the Monthly average paymen of each customer.

The logic I am trying to implement is that 

When Type = Weekly and Frequency is 1 (pay every week), then the monthly amount should be = Amount * count of plan purchased * 4 (becauie there are 4 weeks in a month)

When Type = Weekly and Frequency is 2 (pay every 2 week), then the monthly amount should be = Amount * count of plan purchased * 2 (becauie there are 2* 2 weeks in a month)

Whenn Type = Monthly and Frequency is 1, then the monthly amount should be = Amount * count of plan purchased * 1

Whenn Type = Monthly and Frequency > 1, then the monthly amount should be = Amount * count of plan purchased divided frequncy (in the example here the frequency is 12, however, there are 2,3,4 etc in the real data)

May I know if anyone have an idea how to write such logic in a calculated column OR measure?

Thank you

[Anonymous · Accepted solution]

Hi @ljx0648 ，

I create a table as you mentioned.

Then I create a calculated column and here is the DAX code.

Column = 
SWITCH (
    TRUE (),
    'Table'[Type] = "Weekly"
        && 'Table'[Frequency] = 1,
        'Table'[Amount] * 'Table'[Count of Plan Purchased] * 4,
    'Table'[Type] = "Weekly"
        && 'Table'[Frequency] = 2,
        'Table'[Amount] * 'Table'[Count of Plan Purchased] * 2,
    'Table'[Type] = "Monthly"
        && 'Table'[Frequency] = 1,
        'Table'[Amount] * 'Table'[Count of Plan Purchased] * 1,
    'Table'[Type] = "Monthly"
        && 'Table'[Frequency] > 1,
        'Table'[Amount] * 'Table'[Count of Plan Purchased] / 'Table'[Frequency],
    BLANK ()
)

Finally you will get what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[djurecicK2]

Hi @ljx0648 ,

 You could look into using a SWITCH function to achieve this in a measure.

https://learn.microsoft.com/en-us/dax/switch-function-dax