Calculating Rate by Month/Overall (example data inside)

[plate201]

I have a series of date times. An indicator of whether the customer converted or not, every row is a customer.

Column A             Column B         Column C

2021-09-04 18:14 |Customer 1 | Converted
2021-09-05 12:13 | Customer 2 | Not Converted

1.1 how would i calculate the overall conversion rate and how would i put that in a plot?I figure it would be count(convertedornot)/count(totalcustomers). would i need to calculate this using dax then display it using a scorecard? what would the code/steps be based on the table above?

1.2 how do I plot conversion rate by month instead of overall? if I wanted to divide everything up by month and calculate individual conversion rates for all the months? would i need to create a new column and remove the day and time, or can i use a filter or something?

-------------------------------------------------------------------------------------------------------------

Column A                Column B

2021-09-04 18:14 | Direct
2021-09-05 12:13 | indirect

2.1 How would I plot the number of direct customers over time. If i wanted to do it by month would i need to create a new column with just year and month and then count the instances of "Direct" somehow in column B? what would be the steps required here as I am not sure how to do this.

[parry2k · Accepted solution]

@plate201 first and foremost you need calendar table in your model and you can create one following my blog post Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutio...

In Transform data, change your ColumnA to date and apply the changes. set relationship between calendar table and your table on the date column.

Add a following measure to get converted %

Count Rows = COUNTROWS ( yourTable )

% Converted = 
DIVIDE ( 
CALCULATE ( [Count Rows], YourTable[ColumnC] = "Converted" ),
CALCULATE ( [Count Rows], ALLSELECTED ( YourTable[ColumnB] )
)

To visualize, you can use any visual, let's say it is a line chart, on the x-axis using the Month column from the Calendar table and % Converted measure on value and it will get the converted trend.

Start with this and then can move to others which should be pretty straightforward

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

.