6 Replies Latest reply on Apr 7, 2017 2:21 PM by Glenn Kuly

# Total Sales with YoY Label

I want to show total sales, and the use a YoY growth as a label. I've achieved that but previous year must be selected in the filter but by doing so, previous year's sales (in yellow) are also shown. How can I get rid of the yellow bars and only display the current year with the YoY growth labels?

• ###### 1. Re: Total Sales with YoY Label

-- Glenn

• ###### 2. Re: Total Sales with YoY Label

unfortunately i cannot, i'm currently just dragging the sales measure to labels and changing calculation to YoY Growth. It looks like this has to be done by calculated field?

(ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1))

I found this "formula" but when I use it on my chart, the calculations are wrong and the first column doesn't calculate a value. Can you explain how the formula works? Maybe i can use the logic to adjust? The above calculation as a calculated field turns my chart into this:

• ###### 3. Re: Total Sales with YoY Label

It's a percentage change formula, but it's impossible to tell from the image what it's based on, or what accounts for the two bar colors and how you've set up the labeling.

A posted workbook would be a huge help.

-- Glenn

• ###### 4. Re: Total Sales with YoY Label

I wish I could share the workbook but unfortunately I cannot. I know it would be super easy to send the workbook and let the pros figure it out in a min =(

The formula above is your simple % growth formula, (X2 - X1) / X1 but what does the ZN, Lookup, and -1 mean?

• ###### 5. Re: Total Sales with YoY Label

Hi Andrew,

It's difficult to investigate without data, anyways here is my guess.

// Only filtering display, not filter data

[Filter]

lookup(min(year([Order Date1])),0)

Thanks,

Shin

• ###### 6. Re: Total Sales with YoY Label

ZN replaces null values with zero, and the Lookup -1 equals the value one row higher in the table.

I've used the calculated field for annual YoY sales growth in the following dual-axis chart, with the label on top (which will allow you to use a different label inside the bars).

Not sure what fields you are using for sales and previous year sales, or how you are using the filter, but hope this will be helpful:

-- Glenn