adding totals per order | CLEARIFY

I have been experimenting with and researching/learning about power pivot tables, but I haven't found the solution to my requirement so I am hoping someone can help.

I am using the default sales power pivot example file. I am trying to add a row that gives the order total as well as the sum of the profit and margin % per order. Or maybe adding them as new columns that only display in one cell per order.

Power pivot (and pivot tables in general) are fairly new to me. If anyone has online resources for in-depth learning I would be grateful for links.

Thanks!

Matt

That is actually one of the out of the box examples with the addition of a column or two:

the out of the box example using the add-in looks like this:

Just remove the item name, and move things around like this: 

Now you hove profit margin by document number,  This is filtered by posting transactions, e.g. invoice, credit memo, sales receipts. Use the filter for document transaction date for the appropriate time frame; ditto with customer name.

You will, of course only have COGS for inventory and non-inventory items, as QB can't assign a cost to a particular non-inventory item in a singular posted transaction.  And in fact if you went to the transaction journal on say, an invoice transaction, you would see the same sales, and COGS postings.

QQube contains details at the line item level - so you could add things like item and description, and then subtotal the document number to see everything on the invoice like this:

Rule of thumb:

• Values are things you measure
• Rows are things you measure by
• Pivot table rows automatically sum up the values (or avg, or max, etc.) according to the level of detail the field provides.  

Matt,

If above examples aren't what you are after...feel free to email me fran@freedupdataoptics.com. and we can set up a time to talk.. Be happy to help you out.  I do a lot of powerpivot work. I know how frustrating it is to be stuck on something and can't move forward. Been there.

Fran

On 2/18/2018 9:16 PM, CLEARIFY ® said:

subtotal the document number to see everything on the invoice like this

This is what I am trying to do. Can you please explain what I need to do to get the subtotal per invoice like your example?

Thanks so much for the thorough explanation!

Thanks, Fran. I appreciate your offer and if I get to that point I will definitely contact you!

Also, how would I add sales tax into the invoice subtotal?

This guide shows you how to do this: https://clearify.com/wiki/view/105/design-options

That whole section on Creating Pivot Tables will also assist you.

I also would highly recommend fran on training/working with pivot tables - she is the what we call the 'local QQube and PowerPivot/Excel Guru, and has a ton of tricks up her sleeve.  Might save you hours of consternation.

This one is a little tricky, because we don't have sales tax by line(item) - even QB doesn't do that.  We have reverse engineered that in our Sales Tax Subject, but it is overkill here.

We have a document sales Tax figure that you can pull in - put don't pull it in the values area.  Just bring it in the labels area - otherwise you will sum up the sales tax total figure again and again for each line you have exposed in your pivot table.

Another way to do it, is bring it in the values area, but do a max - and not sum.  Problem is, it won't give you a grand total at the bottom.  It would be nice if there was a 'distinct value' mechanism in a regular pivot table, but there isn't

And still another way is to use a PowerPivot table and divide the document sales tax figure by the number of lines exposed so that you get true totals.  That is something you would need Fran for - that is right up her alley.