Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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 !
User | Count |
---|---|
84 | |
77 | |
64 | |
51 | |
46 |
User | Count |
---|---|
101 | |
43 | |
39 | |
39 | |
36 |