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

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

Reply
NicoHa
Regular Visitor

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     
DateManager in chargeProductQuantityUnit Price Sales Amount
1/01/2022Manager 3product A5€ 10,00€ 50,00
1/01/2022Manager 3product B3€ 50,00€ 150,00
1/01/2022Manager 3product C6€ 38,00€ 228,00
2/01/2022Manager 1product A10€ 10,00€ 100,00
2/01/2022Manager 1product B0€ 50,00€ 0,00
2/01/2022Manager 1product C3€ 38,00€ 114,00
3/01/2022Manager 2product A0€ 10,00€ 0,00
3/01/2022Manager 2product B1€ 50,00€ 50,00
3/01/2022Manager 2product C5€ 38,00€ 190,00
4/01/2022Manager 1product A0€ 10,00€ 0,00
4/01/2022Manager 1product B1€ 50,00€ 50,00
4/01/2022Manager 1product C5€ 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 chargeJanFebMar
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!

2 ACCEPTED SOLUTIONS

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.

 

Knipsel2.GIF

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

 

visualvisual

 

You see my problem?

Thanks.

 

View solution in original post

Jayee
Responsive Resident
Responsive Resident

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!!

View solution in original post

5 REPLIES 5
Jayee
Responsive Resident
Responsive Resident

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.

 

Knipsel2.GIF

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

 

visualvisual

 

You see my problem?

Thanks.

 

Jayee
Responsive Resident
Responsive Resident

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!!

that works, thanks!

Jayee
Responsive Resident
Responsive Resident

Great, I think you have mistakenly selected question post as a solution !

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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