Sales Adoption by Month

Power BI
1

Hello,

I would like to build a table much like the table build in the time based cohort analysis training that was done here.

The difference is instead of analyze customer churn, I would like to analyze sales adoption by period.

My definition of sales adoption is the following:

Monthly sales in period 1 / Max monthly sales for customer

For example:

Customer 1: Max Monthly sales = 100
Month 1 Sales = 15
Month 2 Sales = 30
Month 3 Sales = 60
Month 4 Sales = 100

In Month 1 Sales Adoption would be 15% and so on.

This will give me an idea of the adoption over time by customer.

Thank you,
Mark

2

Hi @msedlak,

You can try something like this.

Max Monthly by Customer = 
MAXX(
    ADDCOLUMNS(
        SUMMARIZE( Sales, Customers[Customer Name Index], Dates[Month & Year] ),
        "@Monthly Sales", [Total Sales] ),
    [@Monthly Sales] 
)

In a Matrix visual place the Customer key on the Rows and the Month & Year on the columns

Sales adoption = 
    DIVIDE( [Total Sales], CALCULATE([Max Monthly by Customer], ALLSELECTED( Dates )) )

I hope this is helpful.

3

Hi @msedlak, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

4

I’m sorry, I got tied up on a few projects and I have not had the chance to run this to ground. I will do this tomorrow morning. Thank you @Melissa for the prompt response and thank you for checking in.

5

@Melissa, This is great! There are so many great formulas and so much to learn here.

This work, for the majority of the customers. However, there are some random months with Infinity for the max sale amount. I did check and there were sales in that month for the customer.

Do you have any idea what may be the cause of this?

6

Hi @msedlak,

Can you share a sample PBIX? Use the search option (magnifying glass) to lookup the video for how to “Mask your data” if that is an issue.

DIVIDE handles division by zero errors by returning a blank value instead of infinity (or whatever value you’ve specified as it’s last parameter).

7

I work in Healthcare and there are too many places with sensitive data and I would be afraid I’d miss one.

What you provided gets me to where I need to go. I really appreciate your help @Melissa