14 Replies Latest reply: Mar 22, 2017 12:06 PM by Sunny Talwar

Pivot Table Total based on different column

m m Mar 13, 2017 11:35 AM

SchoolSubjectStudentsGradesABCMathab0ABCMathac12ABCMathad32ABDMathae1BBEnglishuu44BBEnglishud0BBEnglishua

School

Subject

Students

Grades

School	Subject	Students	Grades	Class Total	Variance
ABC	Math	ab	10	55	10/55
ABC	Math	ac	12	55	12/55
ABC	Math	ad	32	55	32/55
ABD	Math	ae	1	55	1/55
BB	English	uu	44	87	44/87
BB	English	ud	10	87	10/87
BB	English	ua	33	87	33/87

How can i calculate Class Total. will it be formulat or set analysis.

Re: Pivot Table Total based on different column

Sunny Talwar Mar 13, 2017 11:38 AM (in response to m m)

Should be like this

Class Total
Sum(TOTAL <School> Grades)

Variance
Sum(Grades)/Sum(TOTAL <School> Grades)
Like Show 1 Like (1)

Actions
- Re: Pivot Table Total based on different column
  
  Vishwarath Nagaraju Mar 13, 2017 11:49 AM (in response to Sunny Talwar )
  
  I think mm has to clarify if it is typo or expecting to be aggr based on School. Coz his school name has ABD (will it be ABC or ABC only) as his total is 55.
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Pivot Table Total based on different column
    
    Sunny Talwar Mar 13, 2017 11:52 AM (in response to Vishwarath Nagaraju)
    
    That seems like a typo... but it might be aggr on School and Subject I think
    
    Class Total
    Sum(TOTAL <School, Subject> Grades)
    
    Variance
    Sum(Grades)/Sum(TOTAL <School, Subject> Grades)
    
    Like Show 1 Like (1)
    
    Actions
    - Re: Pivot Table Total based on different column
      
      m m Mar 21, 2017 2:52 PM (in response to Sunny Talwar )
      
      Yes that was typo.
      
      and i am not getting expected results. i believe we may have to use aggr function to get total by school count.
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: Pivot Table Total based on different column
        
        m m Mar 21, 2017 2:53 PM (in response to m m)
        
        in my case i forgot to mention. Class Total field don't exist. I have to determine class total by School name and based on that used the total.
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Pivot Table Total based on different column
        
        m m Mar 21, 2017 2:56 PM (in response to m m)
        
        School Subject Students Grades Variance
        
        ABC Math ab 10 10/55
        ABC Math ac 12 12/55
        ABC Math ad 32 32/55
        ABC Math ae 1 1/55
        BB English uu 44 44/87
        BB English ud 10 10/87
        BB English ua
        33
        33/87
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Pivot Table Total based on different column
        
        Vishwarath Nagaraju Mar 21, 2017 3:06 PM (in response to m m)
        
        What is the problem you are facing? Did you try Sunny's below expressions?
        Dim:
        School
        SUbject
        Students
        Grades
        Exprs:
        ClassTotals
        Sum(TOTAL <School> Grades)
        
        Variance
        Sum(Grades)/Sum(TOTAL <School> Grades)
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Pivot Table Total based on different column
        
        m m Mar 21, 2017 3:14 PM (in response to Vishwarath Nagaraju)
        
        sorry i did not have total field. I was just trying to explain where it was comming from. Your expression don't give me right results. I think we will have to use Aggr function to get this results.
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Pivot Table Total based on different column
        
        Sunny Talwar Mar 21, 2017 3:18 PM (in response to m m)
        
        What is wrong here my friend?
        
        253098.qvw 152.2 K
        
        Like Show 1 Like (1)
        
        Actions
        
        Re: Pivot Table Total based on different column
        
        Sunny Talwar Mar 21, 2017 3:19 PM (in response to Sunny Talwar )
        
        In case Aggr() makes you happy (which doesn't make me happy because it effects performance), you can use this
        
        Sum(Grades)/Aggr(NODISTINCT Sum(Grades), School)
        
        253098.qvw 153.0 K
        
        Like Show 1 Like (1)
        
        Actions
        
        Re: Pivot Table Total based on different column
        
        m m Mar 22, 2017 12:02 PM (in response to Sunny Talwar )
        
        Hi Sunnny
        
        Would the formula work the same way if aggrigation was "Count" and not "SUM"
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Pivot Table Total based on different column
        
        Sunny Talwar Mar 22, 2017 12:02 PM (in response to m m)
        
        I believe it would...
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Pivot Table Total based on different column
        
        m m Mar 22, 2017 12:02 PM (in response to m m)
        
        Class Total
        count(TOTAL <School, Subject> Grades) ?
        
        Variance
        count (Grades)/Sum(TOTAL <School, Subject> Grades) ?
        
        or do we have to rewrite it differently.
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Pivot Table Total based on different column
        
        Sunny Talwar Mar 22, 2017 12:06 PM (in response to m m)
        
        More like this
        
        Class Total
        Count(TOTAL <School> Grades)
        
        Variance
        Count(Grades)/Count(TOTAL <School> Grades)
        
        Like Show 0 Likes (0)
        
        Actions

Go to original post