PowerPivot columns question

[cr731]

I'm trying to build a pivot table of data that contains Actuals and Budgets by month. My goal is to show Actuals by month across the columns, and then show just year totals for budget. So it would be like Jan, Feb, Mar... Dec of Actuals, and then just a 2016 column of Budget. I'm getting stuck on how to achieve this, short of having a separate column for each month and the year total... which is not ideal. How can I build my data table or use calculated measures in order to accomplish this?

Thanks

 

[Joyner]

I would have to see your data layout and what you have now in the PT, because sorry but I'm not getting a good sense of it from your description.

But I'm guessing for the Budget you can Group the months. Depending on if they are actual dates or not, you can just Right-Click on the Budget Date field and select Group, then group by Year (you can also group by month and year to maintain some flexibility) Then you can use Year like any other field. If these are not actual dates, you should be able to manually select all of the Budget months in the PT, then select Group, name it "2006" or whatever you want.

 

[Matt Allington]

You can do this by selecting Fields, Items and Sets from the Pivot stable menu. Select create set based on Columns and then delete the columns you don't want by month for budget just leaving the year. give he set a name and you can reuse it again in other Pivots - it will be in the filed list.