5 Replies Latest reply: Aug 23, 2017 5:19 PM by harlow hunt RSS

    Difficulty with Lift % Expression

    harlow hunt

      I am having an issue calculating a Lift % formula.  Typical lift % formula is something like this (2017 sales - 2016 sales) / 2016 sales.

       

      For this lift % formula in question, I am trying to determine the lift by avg. ticket sales $

       

      The 1st problem is that I have 2 different locations (Location 'A Retail' and location 'B Retail').  So I cannot simply use a Set Analysis and just use the Avg sales dollars.  That would give me an incorrect figure.  For instance, if the average ticket sale is $20 in location A and $22 in location B, then the average ticket when combining *both* locations would be roughly $21.  But if I used set analysis and used the average function of both places, I would end up getting an incorrect number of $42.

       

      What I have found works for an expression to come up with avg. ticket $ is this:

       

      (Sum({$<Location_Year={"A Retail2017"}>}[Sales Amount]) + Sum({$<Location_Year={"B Retail2017"}>}[Sales Amount]))  /  (Count({$<Location_Year={"A Retail2017"}>}[Trans ID])+Count({$<Location_Year={"B Retail2017"}>}[Trans ID]))


      This expression basically sums up the sales from both retail locations and divides the count of tickets (Trans ID) from both locations.


      However, now I'm trying to calculate the Lift % of the avg. ticket sales $ from 2017 vs. 2016.  This is the expression I tried using, but it  keeps giving 0.0% on every metric:


      ((Sum({$<Location_Year={"A Retail2017"}>}[Sales Amount])+Sum({$<Location_Year={"B Retail2017"}>}[Sales Amount]))/(Count({$<Location_Year={"A Retail2017"}>}[Trans ID])+Count({$<Location_Year={"B Retail2017"}>}[Trans ID]))

      -

      (Sum({$<Location_Year={"A Retail2016"}>}[Sales Amount])+Sum({$<Location_Year={"B Retail2016"}>}[Sales Amount]))/(Count({$<Location_Year={"A Retail2016"}>}[Trans ID])+Count({$<Location_Year={"B Retail2016"}>}[Trans ID])))

      /

      (Sum({$<Location_Year={"A Retail2016"}>}[Sales Amount])+Sum({$<Location_Year={"B Retail2016"}>}[Sales Amount]))/(Count({$<Location_Year={"A Retail2016"}>}[Trans ID])+Count({$<Location_Year={"B Retail2016"}>}[Trans ID]))

       

      I'm guessing that I have the parentheses in the incorrect spot.  If anybody could help, I would appreciate it.

       

       

       

      Rich

        • Re: Difficulty with Lift % Expression
          Sunny Talwar

          May be this with one extra parenthesis to begin with

           

          (((Sum({$<Location_Year={"A Retail2017"}>}[Sales Amount])+Sum({$<Location_Year={"B Retail2017"}>}[Sales Amount]))/(Count({$<Location_Year={"A Retail2017"}>}[Trans ID])+Count({$<Location_Year={"B Retail2017"}>}[Trans ID]))

          -

          (Sum({$<Location_Year={"A Retail2016"}>}[Sales Amount])+Sum({$<Location_Year={"B Retail2016"}>}[Sales Amount]))/(Count({$<Location_Year={"A Retail2016"}>}[Trans ID])+Count({$<Location_Year={"B Retail2016"}>}[Trans ID])))

          /

          (Sum({$<Location_Year={"A Retail2016"}>}[Sales Amount])+Sum({$<Location_Year={"B Retail2016"}>}[Sales Amount]))/(Count({$<Location_Year={"A Retail2016"}>}[Trans ID])+Count({$<Location_Year={"B Retail2016"}>}[Trans ID]))

          • Re: Difficulty with Lift % Expression
            harlow hunt

            Unfortunately that creates an error in the expression.

             

             

             

             

            Rich

              • Re: Difficulty with Lift % Expression
                Sunny Talwar

                How about this

                 

                ((Sum({$<Location_Year={"A Retail2017"}>}[Sales Amount])+Sum({$<Location_Year={"B Retail2017"}>}[Sales Amount]))/(Count({$<Location_Year={"A Retail2017"}>}[Trans ID])+Count({$<Location_Year={"B Retail2017"}>}[Trans ID]))

                /

                (Sum({$<Location_Year={"A Retail2016"}>}[Sales Amount])+Sum({$<Location_Year={"B Retail2016"}>}[Sales Amount]))/(Count({$<Location_Year={"A Retail2016"}>}[Trans ID])+Count({$<Location_Year={"B Retail2016"}>}[Trans ID]))

                -

                1

              • Re: Difficulty with Lift % Expression
                harlow hunt

                That still gives an error in the expression.  However, I think I started to pinpoint some of the issue.

                 

                For example, the 2017 avg. ticket sale comes out to $10.31 vs. 2016 of $10.51 since.  The difference is -$0.19 with the lift supposed to be -1.81%.

                 

                When I type in the following expression:

                 

                 

                ((Sum({$<Location_Year={"A Retail2017"}>}[Sales Amount])+Sum({$<Location_Year={"B Retail2017"}>}[Sales Amount]))/(Count({$<Location_Year={"A Retail2017"}>}[Trans ID])+Count({$<Location_Year={"B Retail2017"}>}[Trans ID])))

                -

                ((Sum({$<Location_Year={"A Retail2016"}>}[Sales Amount])+Sum({$<Location_Year={"B Retail2016"}>}[Sales Amount]))/(Count({$<Location_Year={"A Retail2016"}>}[Trans ID])+Count({$<Location_Year={"B Retail2016"}>}[Trans ID])))

                 

                It gives me the correct dollar difference (-$0.19).

                 

                However, when I try to then divide that by the 2016 avg ticket price (example below):

                 

                /

                ((Sum({$<Location_Year={"A Retail2016"}>}[Sales Amount])+Sum({$<Location_Year={"B Retail2016"}>}[Sales Amount]))/(Count({$<Location_Year={"A Retail2016"}>}[Trans ID])+Count({$<Location_Year={"B Retail2016"}>}[Trans ID]))) 

                 

                 

                That is where it doesn't come in correctly.  In fact, the end result is now 930.9% (instead of -1.81%).

                 

                 

                When I switched the format over to 'money', it reads $9.31.  That is $1 off from the 2017 avg. ticket sales.

                 

                In fact, every metric does the same thing...it reads $1 off from the 2017 avg. ticket sale. 

                 

                So there must be something going on when I divide the difference by the 2016 avg. ticket sale in the expression.

                  • Re: Difficulty with Lift % Expression
                    harlow hunt

                    Okay, I just figured it out.

                     

                    Notice the parentheses.  For the margin difference (all of the steps prior to the division)...I start off with 3 parentheses.  At the end of the first part (just before the division symbol), I put it 4 parentheses.

                     

                    (((Sum({$<Location_Year={"OIA (A) Retail2017"}>}[Sales Amount])+Sum({$<Location_Year={"OIA B Retail2017"}>}[Sales Amount]))/(Count({$<Location_Year={"OIA (A) Retail2017"}>}[Trans ID])+Count({$<Location_Year={"OIA B Retail2017"}>}[Trans ID])))

                    -

                    ((Sum({$<Location_Year={"OIA (A) Retail2016"}>}[Sales Amount])+Sum({$<Location_Year={"OIA B Retail2016"}>}[Sales Amount]))/(Count({$<Location_Year={"OIA (A) Retail2016"}>}[Trans ID])+Count({$<Location_Year={"OIA B Retail2016"}>}[Trans ID]))))

                    /

                    ((Sum({$<Location_Year={"OIA (A) Retail2016"}>}[Sales Amount])+Sum({$<Location_Year={"OIA B Retail2016"}>}[Sales Amount]))/(Count({$<Location_Year={"OIA (A) Retail2016"}>}[Trans ID])+Count({$<Location_Year={"OIA B Retail2016"}>}[Trans ID])))

                     

                     

                     

                     

                    Rich