Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
derekli17001
Helper I
Helper I

How to get the monthly average sales of a customer

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!

2 ACCEPTED SOLUTIONS
Ritaf1983
Super User
Super User

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

Ritaf1983_0-1723812258907.png

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

Ritaf1983_1-1723812398581.png

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]))
Ritaf1983_2-1723813772352.png

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

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.

Ritaf1983_0-1724206799690.png

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

9 REPLIES 9
Ritaf1983
Super User
Super User

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

Ritaf1983_0-1723812258907.png

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

Ritaf1983_1-1723812398581.png

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]))
Ritaf1983_2-1723813772352.png

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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

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.

Ritaf1983_0-1724206799690.png

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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!

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]))
Ritaf1983_0-1724987898578.png

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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]))
Ritaf1983_0-1724987898578.png

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile
jgeddes
Super User
Super User

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.