Forum Discussion

rdurrell's avatar
rdurrell
Frequent Visitor
1 year ago
Solved

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:

 

CompanyYearMonthSectorCustomers
ABC Co20241Personal10
ABC Co20241Business5
ABC Co20242Personal20
ABC Co20242Business5

 

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

  • rdurrell create this measure:

     

    My Measure = 
    IF ( ISINSCOPE ( Table[Month] ) || ISINSCOPE ( Table[Sector] ), 
          SUM ( Table[Customers] ),
          AVERAGE ( Table[Customers] )
    )
    • rdurrell's avatar
      rdurrell
      Frequent 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