Forum Discussion

xliu1's avatar
xliu1
Icon for Helper II rankHelper II
5 years ago
Solved

Level of Detail Calculation

I try to build a matrix visualization that shows the percentage of subtotal for each category within a table like this: 

So the total of On Campus, High Flex, and Off Campus should sum to 100% for both undergraduate and graduate.

 

Here are the formula I used:

ModalityPerc = DIVIDE(SUM(Aggr_Headcount[Section_Count]), [Section_Total])
Section_Total = CALCULATE (SUM ( Aggr_Headcount[Section_Count] ), ALLSELECTED(Aggr_Headcount))
 
"ModalityPerc" is the value I placed in matrix table. But the formula does not give me the correct results. I have other filters on the same page, so I need the calculation to be changed dynamically with the filters applied. For whatever the filter selected, the sum of On Campus, High Flex, and Off Campus within undergraduate/graduate should always add up to 100% for each college. I hope I explain it clearly and get some help with this problem! Thanks.

 

 
 
Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    5 years ago

    xliu1  What's the name of the column where High Flex/Off Campus/On Campus info comes from? Put that in the ALLSELECTED instead of your entire table and see if that helps.

2 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    xliu1  What's the name of the column where High Flex/Off Campus/On Campus info comes from? Put that in the ALLSELECTED instead of your entire table and see if that helps.

    • xliu1's avatar
      xliu1
      Icon for Helper II rankHelper II
      5 years ago

      Thank you for your response! It is the column of course site that has info High Flex/Off Campus/On Campus. I put that column in the ALLSELECTED, and I got results as I want.

      Section_Total = CALCULATE (SUM ( Aggr_Headcount[Section_Count] ), ALLSELECTED(Aggr_Headcount[Course_Site]))