The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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.
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.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
153 | |
118 | |
116 | |
73 | |
61 |
User | Count |
---|---|
246 | |
117 | |
104 | |
98 | |
93 |