Display Different summarizations by level in a matrix visual

[rdurrell]

I have a data set that has 5 different columns. Year, month, sector, company, and customer count. See below for an example:

Company	Year	Month	Sector	Customers
ABC Co	2024	1	Personal	10
ABC Co	2024	1	Business	5
ABC Co	2024	2	Personal	20
ABC Co	2024	2	Business	5

Now, I have a matrix visual where I am nesting Sector into Month into Year, and my values are the customer counts. That is, when I click 2024, it will show me months 1 and 2 customers, and then when I click on each month, it will show me the 2 sector customers.

The issue I am having is that I want different summarizations for each level of the matrix. For the sector, I want the data shown as a sum (it is doing this already). For the month, I want the data shown as a sum of the sectors (it is doing this as well). However, when I roll everything up to the year, I want it to display an AVERAGE of the months, not the SUM, which it is currently doing. 

Using the data I have above, this means when I drill down to 2024 in month 1, the visual will show 10 for personal and 5 for business, and in month 2 it will show 20 for personal and 5 for buisiness. When I drill down to just the months of 2024, it would show 15 for month 1 and 25 for month 2. Then, when I roll up to just the year 2024, it will show 20. Currently, it shows 15+25=40, which is a sum of months 1 and 2. I want an average of months 1 and 2. 

How can I do this? I appreciate any help

[parry2k · Accepted solution]

@rdurrell create this measure:

My Measure = 
IF ( ISINSCOPE ( Table[Month] ) || ISINSCOPE ( Table[Sector] ), 
      SUM ( Table[Customers] ),
      AVERAGE ( Table[Customers] )
)

[rdurrell]

I had to change the formula around to take the sum and divide by 12 to get the avg per year, but your code was still very helpful!! THank you