Difficulty with Lift % Expression

[Anonymous]

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

[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]))

[Anonymous]

Unfortunately that creates an error in the expression.

Rich

[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

[Anonymous]

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.

[Anonymous · Accepted solution]

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