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: 2

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,553
Office Version
  1. 365
Platform
  1. Windows
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.
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,096
Office Version
  1. 2016
Platform
  1. Windows
Can you place FY by ROWS - select a cell in the row ex. FY20 - Calculations --> Calculated Item - add your calculation - move FY to COLUMNS
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,096
Office Version
  1. 2016
Platform
  1. Windows
Sorry when added to datamodel the previous suggestion don't work.
 

pdph77

New Member
Joined
May 3, 2016
Messages
12

ADVERTISEMENT

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
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,096
Office Version
  1. 2016
Platform
  1. Windows
=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"))
 

pdph77

New Member
Joined
May 3, 2016
Messages
12
=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?
 

Forum statistics

Threads
1,143,842
Messages
5,721,116
Members
422,340
Latest member
canadianbacon357

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
Top