How to get the monthly average sales of a customer

[derekli17001]

Hello, i have a sample sheet here

I dont expect anyone to do the work for me but i wish to be pointed in the right direction.

Based off this excel data sheet - i wish to know how to find out the monthly average sales amount that customers put in, in a power bi visual. So basically calculates the average sales an average customer makes on a monthly basis into a card visual. Thanks!

[Ritaf1983 · Accepted solution]

Hi @derekli17001 
First, you have the sales data. Once you convert the column to numbers, it will generate rows with errors. So, clean it as needed - filter out, replace with 0, or replace with blank. (I filtered them out  from PQ.)

2. Create a relationship between the sales table and calendar table

if you don't have calendar table you can copy it from my PBIX 
More information about creating and using calendar table here :
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

3. Create a measure with the formula :

Average sales for customers =

Var months_ = DISTINCTCOUNT('Calendar'[Year-Month])

Var Total_sales = Sum('Data'[Sales])

Var Customers = DISTINCTCOUNT('Data'[Client])

Return

CALCULATE(Total_sales/ months_/Customers,'Calendar'[Date]<=max('Data'[Sales]))

The pbix is attached

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

[Ritaf1983 · Accepted solution]

Hi @derekli17001 
First, you have the sales data. Once you convert the column to numbers, it will generate rows with errors. So, clean it as needed - filter out, replace with 0, or replace with blank. (I filtered them out  from PQ.)

2. Create a relationship between the sales table and calendar table

if you don't have calendar table you can copy it from my PBIX 
More information about creating and using calendar table here :
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

3. Create a measure with the formula :

Average sales for customers =

Var months_ = DISTINCTCOUNT('Calendar'[Year-Month])

Var Total_sales = Sum('Data'[Sales])

Var Customers = DISTINCTCOUNT('Data'[Client])

Return

CALCULATE(Total_sales/ months_/Customers,'Calendar'[Date]<=max('Data'[Sales]))

The pbix is attached

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

[derekli17001]

Hi - to confirm this is the amount of sales an average month would have right?

[Ritaf1983]

Hi @derekli17001 
Yes , the logic of the formula : sum of sales / months quantity

[derekli17001]

Thank you for the prompt reply - i tried jgedde's solution below for comparision and the difference between both your calculations was a over 2x as seen here averAGE TEST.pbix
Is there a way to validate either for the most accurate result? Thank you

[Ritaf1983 · Accepted solution]

Hi @derekli17001 

To determine the correct solution, it's important to understand the logic behind it, independent of Power BI. I’ve explained the logic of my approach, but you’ve only mentioned "average."

Before we spend more time following a path without a clear final goal, could you please build a simple table, export it to Excel, and check the results there? If you find any issues with my calculation, I’d appreciate it if you could explain the logic needed to resolve them.

The pbix with the table is attached

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

[derekli17001]

Hello,

Thank you for that. Would it be possible to adjust the measure so that instead of 60 months, its the amount of months that sales activity occurs in? 
For instance, if i want to use the measure for a customer who has sales from Jan 2023 to March 2024, it will divide by 15 months rather than every month(12) of those years? Thank you!

[Ritaf1983]

Hi @derekli17001 
You can modify the formula to this: 

Average sales for customers active months =

Var Total_sales = SUMX('Data','Data'[Sales])

Var months_ =DATEDIFF(min('Data'[Activity Dates]),max('Data'[Activity Dates]),MONTH)

Var Customers = DISTINCTCOUNT('Data'[Client])

Return

CALCULATE(Total_sales/ months_/Customers,'Calendar'[Date]<=max('Data'[Sales]))

The updated pbix is attached

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

[Ritaf1983]

Hi @derekli17001 
You can modify the formula to this: 

Average sales for customers active months =

Var Total_sales = SUMX('Data','Data'[Sales])

Var months_ =DATEDIFF(min('Data'[Activity Dates]),max('Data'[Activity Dates]),MONTH)

Var Customers = DISTINCTCOUNT('Data'[Client])

Return

CALCULATE(Total_sales/ months_/Customers,'Calendar'[Date]<=max('Data'[Sales]))

The updated pbix is attached

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

[jgeddes]

The following measures creates a virtual table that sums the customer spend each month and then takes the averages from that table.

Monthly Client Average Sales = 
var _vTable = 
SUMMARIZE(
    'Table',
    'Table'[Activity Dates].[Year], 'Table'[Activity Dates].[Month], 'Table'[Client],
    "__sales", SUM('Table'[ Sales ])
)
var _result = 
AVERAGEX(
    _vTable,
    [__sales]
)
Return
_result

You will need to replace the 'Table' with your table name along with ensuring the column names match. 

Hope this gets you pointed in the right direction.