Forum Discussion
Average Per Team Per Month from Individual Data
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
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)
3 Replies
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)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!
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.
