calculated column in Pivot Table

pdph77

New Member
Joined
May 3, 2016
Messages
12
Hello

I tried to have calculations of difference between two months in two years in the pivot table from power pivot. I've done some research but couldn't find a way to solve my purpose. Unfortunately, the Power BI is not available only Power Pivot and Power Query.
I remember previously in Pivot Table i could do calculated fields but not in this one they are greyed out. i guess this is because it is coming from Data Model.

I would like the columns of Var FY21-Fy20 and Var % from Pivot. Not sure if this is doable.
example.jpg


FY21 and FY20 is stored in one column.
The actual numbers of December of each year is stored in the same numbers.

For now, i can only think to manually copy out the data and add the 2 columns.

PLease help!

Thanks
 

Attachments

  • 1.JPG
    1.JPG
    35 KB · Views: 3

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Post your sample data using XL2BB. We cannot manipulate data in a picture. Once posted, I am sure that solution is building a measure in Power Pivot.
 
Upvote 0
Can you place FY by ROWS - select a cell in the row ex. FY20 - Calculations --> Calculated Item - add your calculation - move FY to COLUMNS
 
Upvote 0
Sorry when added to datamodel the previous suggestion don't work.
 
Upvote 0
Post your sample data using XL2BB. We cannot manipulate data in a picture. Once posted, I am sure that solution is building a measure in Power Pivot.
Thanks alansidman!!
Posted XL2BB below, hope i did it right...


Book1.xlsx
BCD
2FYMonthAmount
3FY21Dec2445989
4FY20Dec1858712
5FY21Nov2000000
6FY20Nov1800000
7FY21Oct2200000
8FY20Oct1700000
Sheet1
 
Upvote 0
=CALCULATE(Sum([Amount]);Filter('Data';[FY]="FY21"))-Calculate(Sum([Amount]);Filter('Data';[FY]="FY20"))
=(CALCULATE(Sum([Amount]);Filter('Data';[FY]="FY21"))-Calculate(Sum([Amount]);Filter('Data';[FY]="FY20")))/CALCULATE(Sum([Amount]);Filter('Data';[FY]="FY21"))
 
Upvote 0
=CALCULATE(Sum([Amount]);Filter('Data';[FY]="FY21"))-Calculate(Sum([Amount]);Filter('Data';[FY]="FY20"))
=(CALCULATE(Sum([Amount]);Filter('Data';[FY]="FY21"))-Calculate(Sum([Amount]);Filter('Data';[FY]="FY20")))/CALCULATE(Sum([Amount]);Filter('Data';[FY]="FY21"))
Thank you mart37!
i will give it a try. Does this mean for things like this i will need to create measures for each of this? e.g. if there are FY15,FY16,FY17,FY18,FY19,FY20 & FY21?
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top