2 Replies Latest reply on Jun 21, 2018 10:39 AM by Emily Skoraczewski

    Calculation to count line item parts per case by customer

    Emily Skoraczewski

      Hello all,

       

      I am looking for some help on a calculation in my data. I cannot post the workbook because it is proprietary information, but I have some screenshots that I think should help.

       

      What I'm trying to do is create a graph that will show me the number of cases that have at least one Line Item Part Id tied to it.

       

      For example, in the screen shot below. I'm looking to calculate by Dealer ID, the number of cases that have a line item part ID.

       

       

       

      What I thought was going to was is to create a calculation that that would count each line item part id, then total up by Dealer ID.

      The calculation behind AGG(Cases with Part...) is IF [Parts Count] = 0 THEN 0 ELSE 1 END

       

       

      Now, when I try roll up this calucation on the Dealer ID level, which would tell me both: the number of their cases that have >=1 Line Item Part ID AND, how many parts per cases, I can't because it's an aggregate function.

       

      Can anyone help me with this? Being able to report how many cases have Line item parts ID is very helpful.

       

      Thanks in advance!