Formula for a new measure

[tbobolz]

Hi all, I have Excel 2010 on XP.

I have tried to search for this solutionbut can not find it.

I have a column in my powerpivot titled "DISCHARGE - FISCAL YEAR". In the pivot table I have this field in the column label so it returns 2013 then 2014 values. I want to add a new measure that creates a variance field in the column labels that simply subtracts these two values. Similar to our old calculated field: however, I can not get the formula correct as i am somewhat new to PP.

Here's the formula that I obviosly have wrong, but I am not sure how to define the column label to subtract 2013 from 2014. =[DISCHARGE - FISCAL YEAR]=2014-[DISCHARGE - FISCAL YEAR]=2013

Thanks for any education you can offer me.

Terry

 

[cyrilbrd]

Hi,

Have you tried calculated fields? link here.

 

[theBardd]

Do you have a date table, so you can use PARALLELPERIOD? Can you post the workbook, I am struggling to picture the data?

 

[tbobolz]

Hi,

thank for the reply, the file is too big to post. I simply imported a file into powerpivots with headers. There are no DAX calculation in the table at all. I want to use a new measure to create a field for the pivot table. My pivot has Dr's anme in the row labels, number of cases in the vlaues filed, and DISCHARGE - FISCAL YEAR in the column labels.

So this returns in the pivot table Dr. X with 21 cases in 2014 then 38 cases in 2013. I want the new measue I create a variance field in the column labels that subtract 2013 values from 2014. I have achieved this in the past but am having trouble this time around.

Hope this makes sence.

[TABLE="width: 625"]
<COLGROUP><COL style="WIDTH: 365pt; mso-width-source: userset; mso-width-alt: 17773" width=486><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" span=2 width=81><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><TBODY>[TR]
[TD="width: 486"]Sum of 1-CASES[/TD]
[TD="width: 114"]Column Labels[/TD]
[TD="width: 81"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2014[/TD]
[TD]2013[/TD]
[TD="class: xl68, bgcolor: transparent"]Case [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]2nd Quarter[/TD]
[TD]2nd Quarter[/TD]
[TD="class: xl68, bgcolor: transparent"]Varaince[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65"]Advanced Family Health, PLLC[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66"]BULMAHN, LORA, MD[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]38[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]-17[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65"]Advanced Health Care Center[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66"]WU,JOHN V MD[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65"]AIKIN, ROBERT MD[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66"]AIKIN, ROBERT MD[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]53[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

 

[tbobolz]

Hi, thanks for the reply,however, calculated field were replaced by measures in 2010, otherwise this would have been a peice of cake. I think they may be bringing them back in 2013.

 

[Art15651]

Maybe I'm missing some sort of complication here, but why can't you just create a calculated column in the PowerPivot window?

CaseVariance:=[20132ndQuarter]-[20142ndQuarter]

Just use as appropriate in your pivot table.

 

[tbobolz]

Well the results of 20132ndQuarter and 20142ndQuarter are calucaltions from one column in the data titled DISCHARGE - FISCAL YEAR. I new to measures as in regular pivot tables, I would have highlighted a column, say 20132nd Quarter and choose calculated fields to subtrack the two columns. This is my learning curve issue. Just not sure how to do the this same feature within "measure" of power pivots.

Thnaks for the help.

 

[Art15651]

If you could post just a small sample file with no sensitive data, it would be much easier to help you.

 

[theBardd]

Hi, thanks for the reply,however, calculated field were replaced by measures in 2010, otherwise this would have been a peice of cake. I think they may be bringing them back in 2013.

No they weren't. Measures were added to PowerPivot, calculated fields are a function of pivot tables, still true in Excel 2010, and they remain so in Excel 2013. Measures have been renamed to Calculated Fields in the latest PowerPivot, but that just means there are two type of Calculated Fields now.

 

[theBardd]

Well the results of 20132ndQuarter and 20142ndQuarter are calucaltions from one column in the data titled DISCHARGE - FISCAL YEAR. I new to measures as in regular pivot tables, I would have highlighted a column, say 20132nd Quarter and choose calculated fields to subtrack the two columns. This is my learning curve issue. Just not sure how to do the this same feature within "measure" of power pivots.

Thnaks for the help.

I think Art15651 is suggesting you abandon the measure, and just create a calculated column in the PowerPivot grid.