Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to 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 :
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
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
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 :
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
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
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
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
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:
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
Hi @derekli17001
You can modify the formula to this:
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
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.
Proud to be a Super User! | |
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
95 | |
90 | |
35 | |
35 |
User | Count |
---|---|
154 | |
102 | |
82 | |
64 | |
54 |