Forum Discussion
problem with a calculation
Hello,
Who can help me out, I'm new at Power BI?
I have a sales table
Let’s say that we sell 3 products (A, B, C).
Every day 1 manager is in charge.
My table looks like this:
| Sales Table | |||||
| Date | Manager in charge | Product | Quantity | Unit Price | Sales Amount |
| 1/01/2022 | Manager 3 | product A | 5 | € 10,00 | € 50,00 |
| 1/01/2022 | Manager 3 | product B | 3 | € 50,00 | € 150,00 |
| 1/01/2022 | Manager 3 | product C | 6 | € 38,00 | € 228,00 |
| 2/01/2022 | Manager 1 | product A | 10 | € 10,00 | € 100,00 |
| 2/01/2022 | Manager 1 | product B | 0 | € 50,00 | € 0,00 |
| 2/01/2022 | Manager 1 | product C | 3 | € 38,00 | € 114,00 |
| 3/01/2022 | Manager 2 | product A | 0 | € 10,00 | € 0,00 |
| 3/01/2022 | Manager 2 | product B | 1 | € 50,00 | € 50,00 |
| 3/01/2022 | Manager 2 | product C | 5 | € 38,00 | € 190,00 |
| 4/01/2022 | Manager 1 | product A | 0 | € 10,00 | € 0,00 |
| 4/01/2022 | Manager 1 | product B | 1 | € 50,00 | € 50,00 |
| 4/01/2022 | Manager 1 | product C | 5 | € 38,00 | € 190,00 |
I want to calculate the average sales amount per day and per manager, within any given time period (month, quarter, year).
I would like to create a visual that looks like this:
| average sales amount per day | |||
| Manager in charge | Jan | Feb | Mar |
| Manager 1 | € 75,00 | € 105,00 | € 89,00 |
| Manager 2 | € 80,00 | € 90,00 | € 75,00 |
| Manager 3 | € 142,00 | € 160,00 | € 88,00 |
Thanks!
Hello,
That's not exactly what I'm looking for.
Lets take a look at Manager 1 as an example.The Total Sales Amount for 02/01/2022 is € 214.
The Total Sales Amount for 04/01/2022 is € 240.
What I need, is the average per day and per Manager.
So for Manager 1, for January, this average is € 227 per day.If I try your solution, I get € 75,67 (the average of all the items):
visual
You see my problem?
Thanks.
Try Below Measure
Average Sales = DIVIDE(SUM('Table'[Sales Amount]),DISTINCTCOUNT('Table'[Date]),0)
If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!!
5 Replies
Hi NicoHa ,
1. Select matrix chart.
2. Add manager fields to rows
3. Add month field in Columns.
4. Add sales amount field in values.
5. There is a drop down arrow on sales amount click and select average insted of sum.
If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!!
Hello,
That's not exactly what I'm looking for.
Lets take a look at Manager 1 as an example.The Total Sales Amount for 02/01/2022 is € 214.
The Total Sales Amount for 04/01/2022 is € 240.
What I need, is the average per day and per Manager.
So for Manager 1, for January, this average is € 227 per day.If I try your solution, I get € 75,67 (the average of all the items):
visual
You see my problem?
Thanks.
Try Below Measure
Average Sales = DIVIDE(SUM('Table'[Sales Amount]),DISTINCTCOUNT('Table'[Date]),0)
If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!!