CynnieStar
New Member
- Joined
- Aug 28, 2015
- Messages
- 2
I need your help. I am trying to move a data model currently created in separate pivot tables into one data model in powerpivot. From there I am creating pivot tables. However, in the old model, there was a calculated item which is a delta (difference) between values for 2015 and 2014 and 2014 and 2013 for different values: hours, net fees etc. and in old model the formula listed as calcuated item looked like this:
Calculated Item
Solve Order Item Formula
1 'Delta FY14-FY13' ='2014'-'2013'
2 'Delta FY15-FY13' ='2015'-'2013'
3 'Delta FY15-FY14' ='2015'-'2014'
I tried to create the same measure in powerpivot but i get stuck as i think i can only do it for 1 value at a time:
DELTA 2015-2014:=CALCULATE(SUM(GESdata1[PerfHours]);GESdata1[FY]=2015)-CALCULATE(SUM(GESdata1[PerfHours]);GESdata1[FY]=2014)
but i would need the same for [Fees], [GrossFees], and many many other.
What was done in old model via calculated item was simple and worked for all measures:
You could easily make a pivot with values, which were calculating deltas with use of the above mentioned calculated item:
COMPARISON
FY HOURS GROSS FEES NET FEES
Delta FY14-FY13 2.374 -458.438 538.496
Delta FY15-FY13 5.938 509.483 1.138.859
Delta FY15-FY14 3.564 967.920 00.363
Can you help me to create it in powerpivot too?
THanks
Calculated Item
Solve Order Item Formula
1 'Delta FY14-FY13' ='2014'-'2013'
2 'Delta FY15-FY13' ='2015'-'2013'
3 'Delta FY15-FY14' ='2015'-'2014'
I tried to create the same measure in powerpivot but i get stuck as i think i can only do it for 1 value at a time:
DELTA 2015-2014:=CALCULATE(SUM(GESdata1[PerfHours]);GESdata1[FY]=2015)-CALCULATE(SUM(GESdata1[PerfHours]);GESdata1[FY]=2014)
but i would need the same for [Fees], [GrossFees], and many many other.
What was done in old model via calculated item was simple and worked for all measures:
You could easily make a pivot with values, which were calculating deltas with use of the above mentioned calculated item:
COMPARISON
FY HOURS GROSS FEES NET FEES
Delta FY14-FY13 2.374 -458.438 538.496
Delta FY15-FY13 5.938 509.483 1.138.859
Delta FY15-FY14 3.564 967.920 00.363
Can you help me to create it in powerpivot too?
THanks