5 Replies Latest reply on Oct 4, 2018 6:02 AM by Diana Prado

# % Change Current Month vs Rolling 12 Month Avg.

Hello,

This is my first post so please ask questions if I am not being clear enough. I really appreciate your help, just want to start by saying that.

Here is some information about my data:

Column1: Email Campaign Type (ex: news, specials, inspirational, etc)

Column2: Year (ex: 2016, 2017, 2018)

Column3: Month (ex: Jan, Feb, Mar, etc)

Column4: Open (12000, 8345, 1245, etc)

Column5: Delivered (30000, 40000, 20000, etc)

Column6: Click (450, 230, 1000, etc)

(I have lots more values including booking, room nights, conversion, etc)

I want to create a simple chart that shows me the % change from the current month vs the 12 month avg. for each of the different Email Campaign Types, and each of the values (Open, Delivered, Click, and more).

This is what I have so far, but it is obvious to me that the numbers are all wrong:

This is what my table calculation looks like, it is obviously wrong but I don't know how to fix it. I am not even sure I need to be using a table calculation at all:

Thank you so much!

• ###### 1. Re: % Change Current Month vs Rolling 12 Month Avg.

Hi Diana,

Please could you attach a workbook with dummy data so that we could use this and re-upload it back to you?

Thanks,

Mavis

• ###### 2. Re: % Change Current Month vs Rolling 12 Month Avg.

Hi Diana,

For now I'll use the superstore sample data set, using segment and sales, with month and year running along the top.

Right click on the measure field and add in a quick table calculation:

Right click again and make sure it's running table across and also you're looking at the previous 11 points plus the current:

Double click on the columns shelf and type in the table calculation last()

Convert this to discrete:

Bring it to filters and set it to 0:

Then add in the original sum of sales to the crosstab. Finally add in the below calculation to the view and make sure this table calc is running table across:

Ultimately I get this view:

Thanks,

Mavis

1 of 1 people found this helpful
• ###### 3. Re: % Change Current Month vs Rolling 12 Month Avg.

Hi Mavis,

Thank you so much for your help! This really gets me almost there. Now my next question is: How do I get the Value "Difference Between 12 Mo..." to show up as a % difference? When I try to do that, this is what I get:

Finally, I want the numbers greater than 0 to show up as green, and the numbers less than 0 to show up as red.

Thank you again for all of your help, you rock!

Diana

• ###### 4. Re: % Change Current Month vs Rolling 12 Month Avg.

Hi Diana,

The difference calculation is calculating the absolute difference and not the percentage difference, so the calculation needs to change to:

Then format this to percentage.

To get the colour, it depends how you want to colour it, to colour the rows bring the difference field into colour on the marks card. Then click on colour in the marks card and set it to diverging colours, 2 step and also go to the advance section and centre it:

To colour just the column, bring measure values into colour and right click to select use seperate legends:

Then format the % difference using the 2 step colour settings detailed above. Then colour the others as dark colours.

Thanks and please see the attached workbook.

Mavis

1 of 1 people found this helpful
• ###### 5. Re: % Change Current Month vs Rolling 12 Month Avg.

Thank you very much, Mavis!!