0 Replies Latest reply: Sep 26, 2016 7:04 PM by Monica Chhabra RSS

    Set Analysis in Pivot table to calculate the total based on multiple criteria

    Monica Chhabra

      Hi All,

       

      I am attaching an image that is a Pivot table having Fiscal Year Month, ASVP, RSD and Owner Name as rows and I have created a measure count(FTACount).  All of these are the fields in the table in addition to FTACount and Seller Type (Seller Type has following values- Bank NSE, MSE, MMSE).  ASVP, RSD, Owner Name has the name of the people where on the top of hierarchy is the ASVP(Area sales Vice President) that has few people under him i.e. called Regional Sales Director and several under people under RSD called Owner Name.  I am trying to create a measure in my pivot table based on following criteria:

      For Owner Name: If [Seller Type]= 'NSE' and Count(FTACount) >= 40 (in Fiscal Month Year) then result = 3/Fiscal Month Year

      For Owner Name: If [Seller Type]= 'MSE' and Count(FTACount) >=8  (in Fiscal Month Year) then result = 3/Fiscal Month Year

      For Owner Name:  If [Seller Type]= 'MMSE' and Count(FTACount) >= 4 (in Fiscal Month Year) then result = 3/Fiscal Month Year

      For ASVP or RSD (On top of Owner Name) : Sum(FTACount)/No. of Owner Name.  Each ASVP/RSD can have multiple NSE, MSE and MMSE. 

       

      Below is the Set Analysis I have attempted so far and is very far from what I am trying to achieve.  Sorry for prior posts on this.  I

       

      Count({$<[Owner Name] = {"=Count(FTACount) >= Pick(Match([Seller Type],'Bank NSE','MSE','MMSE'),40,8,4)"} >} 3)

       

       

      Please help!

      Thanks and Reagrds,

      Monica