Average Per Team Per Month from Individual Data

[Anonymous]

Hi, 

I am very new to Power BI and have went through previous forum answers but just can't seem to work this out. 

I need to calculate the average number of sales per month per team from 3 separate tables with the following information: 

• Table 1 - Contains individual sales entries for each account, the value, the salesperson, and the month recorded. 
• Table 2 - Contains details of which team each salesperson sits within. 
• Table 3 - Contains the headcount per month for each team. 

I need to find the total number and volume of sales per team, and divide this by the total number of team members in each team for each month. I then intend to show how this has progressed on a line chart (for volume of sales) and bar chart (for total revenue). 

I have attached stripped back tables to show what I mean. Any help would be deeply appreciated on this!! Table 2

Table 1

Table 3

[Ashish_Mathur]

Hi,

Please show the expected result in a simple tabular format.  Also, share data in a format that can be pasted in an MS Excel file.  

[FrankAT · Accepted solution]

Hi @Anonymous 

take a look at the attached pbix file (see bottom).

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Anonymous]

This is fantastic, thank you! Works perfectly. 

Could you help me out by explaining a bit of the logic? Finding it tough to follow. Apologies as this is likely beginner-level stuff, but: 

• Why do we sum the Headcount? There's only 1 value for each team per month, so why do we need to sum? 
• Why do we use MAX on Month and Team? 

Thank you again!