Simple Table: Compare Years Sales - Totals column messing up percentage measure

[SteveW]

Hi 

This is my first post and the first time I've tried to build a report, apologies if I'm asking basic questions!

I'm trying to build a basic table showing sales over the last 3 years and comparing this year with the previous year in the final column (2018 vs 2017) which is a percentage.  I've used measures to achieve this.

The Total column is useful however it's coming in to play when calculating 2018 vs 2017 which is messing up my percentage.  How do I exclude it from this calculation?

(I'm using test data so - it's old so I'm assuming the current year is 2018 for the purpose of building this report.)

Meausures:

• mCurrentYear = sum(if(DTPR_Year =CurrentYear,AF,0))
• mCurrentYearM1 = sum(if(DTPR_Year =CurrentYear-1,AF,0))
• mCurrentYearM2 = sum(if(DTPR_Year =CurrentYear-2,AF,0))
• mCYvsmCYM1 = (sum(if(DTPR_Year=CurrentYear,AF,0))-sum(if(DTPR_Year =CurrentYear-1,AF,0)))/sum(if(DTPR_Year =CurrentYear-1,AF,0))

mCYvsmCYM1 is a bit long winded but I also can't seem to access one measure from another so I had to recalculate those within it to arrive at the %age difference comparison.

Perhaps there's an altogether better way to achieve this than using measures in the first place?

Thanks

Steve

[sunny_talwar · Accepted solution]

"The Total column is useful however it's coming in to play when calculating 2018 vs 2017 which is messing up my percentage.  How do I exclude it from this calculation?"

What exactly are you looking to exclude and from where? The Total for 2018 vs 2017 (21%)? The value should not be shown or are you expecting to see a different number?

[SteveW]

Thanks for the reply.

You know I've just realised I'm being stupid.

This report is correct and my brain needs a reboot.

Sorry for wasting time.

[sunny_talwar]

We all need reboots once in a while.. 🙂