Forum Discussion

plate201's avatar
plate201
New Member
5 years ago
Solved

Calculating Rate by Month/Overall (example data inside)

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.
Need Help
  • parry2k's avatar
    parry2k
    5 years ago

    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 Solutions

     

    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.⚡

    .

     

     

     

     

     

1 Reply

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    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 Solutions

     

    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.⚡

    .