Forum Discussion
Display Different summarizations by level in a matrix visual
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
rdurrell create this measure:
My Measure = IF ( ISINSCOPE ( Table[Month] ) || ISINSCOPE ( Table[Sector] ), SUM ( Table[Customers] ), AVERAGE ( Table[Customers] ) )
2 Replies
- rdurrellFrequent Visitor
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