6 Replies Latest reply: Apr 28, 2017 2:23 PM by james eckstein RSS

    Pivot table expression ignores a pivot table dimension

    james eckstein

      I have a pivot table that looks like the following.

       

      Sales manger and Sales Rep are the dimensions

       

      Sales Dollars= Sum(Sales)

       

      Days in Period=count(DISTINCT {<MonthID={"<=$(=(Max(MonthID)))"}>} ReportingDate)


      Daily Sales=Sum(Sales)/(count(DISTINCT {<MonthID={"<=$(=(Max(MonthID)))"}>} ReportingDate) )

       

      The problem I have here is with my calculation of Days in Period. The number of days in this period is 40, but because of the associative data model Steve smith only made sales on 34 of the 40 days so his days in period is only 34. I need the days in period column to be the same for each Sales Rep. (With the ability to make selections on dates which would change the number of days in period)

       

      Can anyone help????

       

       

       

      Sales ManagerSales RepSales DollarsDays in PeriodDaily Sales
      John DoeSteve Smith100343
      Derek Jeter200297
      Tom Brady3003010
      Jane SmithMike Trout400667
      Lebron James5002520
      Larry Bird6004015
      Steve Nash7004018
      David Ortiz8003523