Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Solved! Go to Solution.
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)
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)
that works, thanks!
Great, I think you have mistakenly selected question post as a solution !
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
142 | |
71 | |
64 | |
52 | |
50 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |