Powerpivots and calculation fields

[Keith_Excel]

I'm very new to Powerpivots and have never really used calculated fields in pivot tables before.

I've just started in a new job and they use pivot tables alot. We've got a new reporting tool which uses PowerPivots and it's very impressive and much quicker than our old reporting tool. However, in our old reporting tool we had a cost & margin analysis, which included fields which worked out the percentage material cost of revenue and the gross margin percntage. I've searched the internet and these forums pages and haven't come up with a solution yet. Either the information hasn't been relevent or the process seem far to complex for something that I would assume would be relatively simple.

The range of the pivot can expland massively from looking at each overseas site location to expanding out over 30,000 different product types. So having a calculation outside the pivot table isn't an option. Please could someone talk me through how to add a column into my pivot table that will work out these margins?

Many thanks Keith

 

[tbobolz]

Hi Keith,

I found that the information you’re asking for is difficult for someone to just walk you through here on the board, it just takes a little too much time to explain. I was in your shoes about 2 months ago, and while there are tons a references on how to do DAX formulas, you'll find that you are missing some fundamental piece that are intuitive to the person explaining the process, but will keep you spinning your wheels as you try to duplicate the process.

I highly recommend paying the $10.00 for the electronic copy of the below power pivot help guide. After I got through about 1/3 of the book it had my up and running with all those "little things" needed to create DAX formulas or what would be similar but much more powerful then the old calculated fields. My life has truly been much easier after spending a Saturday reading and learning from this book. Don't get me wrong, I still have trouble with some advance formulas!!!

Just thought this would help you, and NO I am not connected in any way to the profits of this book. Ha

MrExcel.com | "DAX FORMULAS for PowerPivot" Information Page

Terry</SPAN>