6 Replies Latest reply on Feb 22, 2013 1:49 PM by Jim Thompson

    How does one roll-up a calculation back to a Summary level- and then USE it!??

    Jim Thompson

      So, I'm baffled. I have multiple line items of sales for multiple customers like this:

      CustomerIDPurchaseDateServiceTypeAmount
      2783612/1/20124$          643.90
      2783612/1/20122$          542.29
      2783612/1/20124$          221.85
      2783612/1/20121$          452.37
      2783612/1/20122$          474.18
      2783612/1/20121$               3.03
      2783612/1/20121$            33.37
      2783612/1/20123$          147.78
      2783612/1/20122$          240.84
      2151412/1/20123$          570.04
      2151412/1/20124$          408.15
      2151412/1/20123$          205.10
      2151412/1/20121$            82.58
      2151412/1/20123$          622.39
      2151412/1/20122$          618.81
      and on and onand on and onand on and onand on and on

      ...as you can see the same customer (ID) has multiple lines, and, each line has a ServiceType associated with the purchase- and this is where the wheels come off... at least for me...

       

      But I'm looking for the countD of customers of each of the 4 types.... I need to aggregate at the Customer level the customer's Type (ie is he type 1, type 2, etc) BY THE MAJORITY OF DOLLARS SOLD. Or, in other words, if he bought one type 1 for $500 and 3 type 3 for $30 each, he is a type 1 customer.... (ie it rolls up to the summary customer level).

       

      The GOAL of this exercise is to show in a pie chart the breakdown of COUNT of Customer Types with the count and then sum of the sales revenue...

       

      I'm out of ideas, since, the moment I aggregate (in a calculated field of course) the contributions and then determine the largest and return a type, I cannot use this calculated field as a Dimension....sigh... I can use it as a measure, but you can't filter on it, nor, use it as an angle on the pie chart... Aarrgh!

       

      In short, I think I'm trying to calculate on a lower layer and have the result roll up to the summary (customer) layer but I cannot figure out how to go about doing this... Does anyone have a suggestion?? thanks! (sorry about the long winded explanation)....