Help trying to get averages

[Jamiethow]

Hi all,  

Apologies if I have posted this in the wrong section.

I have a situation where I need to get a few different averages, and they all come from the same source data, in a table called Team Reps.   At the moment I am building a Matrix visualisation which has the different sectors across the top, and the rest is all different measures working its way down.

I also have a Date filter which is not connected to any table and I instead use a VAR in my measures to get the date for any comparisions.

The issue I am having for this example is that I need to the Average Number of Companies per Team, it is currently done manually by creating a pivot table in Excel and then selecting the rows relevant to the sector and getting the average from the bottom of the screen based on the selection.

I have tried a few different ways of looking at it, grouping it in the transform data area, or creating a new summized table, but the issue comes when I try to introduce any Date logic in to the equation as the entire matrix should change based on the month selected.

This is a basic example of the data I am looking at.

Contact	Sector	Team	Company	Join Date
Contact1	Sector1	Team 1	Company 1	14/02/2021
Contact2	Sector2	Team 2	Company 2	17/05/2021
Contact3	Sector3	Team 3	Company 3	21/05/2021
Contact4	Sector1	Team 4	Company 4	07/04/2021
Contact5	Sector2	Team 5	Company 5	30/08/2021
Contact6	Sector3	Team 6	Company 6	09/11/2021
Contact7	Sector1	Team 1	Company 7	14/04/2021
Contact8	Sector2	Team 2	Company 8	01/01/2021
Contact9	Sector3	Team 3	Company 9	25/12/2021
Contact10	Sector1	Team 4	Company 10	05/11/2021

Any help would be appriciated.

Thanks!

[parry2k]

@Jamiethow I think this is what you are looking for, add the following measure:

Avg = COUNTROWS ('Avg' ) / DISTINCTCOUNT('Avg'[Team] )

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@Jamiethow can you share the excel file? It is not easy to provide a solution without understanding how you are getting to these numbers?

[parry2k]

@Jamiethow you are arriving at those numbers?

[Jamiethow]

I am getting them manually by creating a Pivot Table in Excel, selecting all the values in the rows related to each sector and taking a note of the average which is displayed at the bottom of the screen.

[parry2k]

@Jamiethow what result you are looking for?

[Jamiethow]

Hi 

Basically looking for something that would allow me to get the averages which would be affected by the date slicer as well.

Based on the example data that I have posted I would expect to see that 

Sector 1 has an Average of 2
Sector 2 has an Average of 1.5

Sector 3 has an Average of 1.5

But if I was to use the Date slicer and remove the December record I would expect to see

Sector 1 has an Average of 2
Sector 2 has an Average of 1.5

Sector 3 has an Average of 1

Let me know if you need anything else.

Thanks