automationamateur
Board Regular
- Joined
- Jan 3, 2011
- Messages
- 166
See below pivot table format (the 4th column is what I am trying to accomplish). I want to calculate year over year change on percents. I do not want a YOY percentage change I want to know the percentage points YOY difference. I also want this to be a part of the pivot (ie a calculated field, not a manual helper column or anything like that because I have pivot charts working off of it). My problem: I can't use a calculated field because I am not comparing two 'fields' I am comparing a single field but 2017 vs 2018. I also can't use the 'show values as difference from' because this shows the number/percent difference (ie in January 50.3% - 50.1% = .002), since I want percentage points the result should be .2 (ie 50.3% - 50.1 * 100). Is there any way to accomplish this? As always, your help is greatly appreciated. Thanks.
Code:
[TABLE="width: 349"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD] Margin %[/TD]
[TD] Margin %[/TD]
[TD] Variance (pts)[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD="align: right"]50.1%[/TD]
[TD="align: right"]50.3%[/TD]
[TD="align: right"]0.2[/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD="align: right"]50.5%[/TD]
[TD="align: right"]47.0%[/TD]
[TD="align: right"]-3.5[/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD="align: right"]50.1%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]-50.1[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD="align: right"]50.0%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]-50.0[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD="align: right"]49.5%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]-49.5[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD="align: right"]49.3%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]-49.3[/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD="align: right"]47.8%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]-47.8[/TD]
[/TR]
[TR]
[TD]August[/TD]
[TD="align: right"]50.5%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]-50.5[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD="align: right"]49.7%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]-49.7[/TD]
[/TR]
[TR]
[TD]October[/TD]
[TD="align: right"]49.1%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]-49.1[/TD]
[/TR]
[TR]
[TD]November[/TD]
[TD="align: right"]49.4%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]-49.4[/TD]
[/TR]
[TR]
[TD]December[/TD]
[TD="align: right"]47.5%[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]-47.5[/TD]
[/TR]
</tbody>[/TABLE]