My boss needs a sales table/report where the data is split into sections (vertically) by month and product. Each month has a series of products sold to a series of clients so the sections will have columns across to calculate total sales for each client. This means that the section for each client will have a quantity column, price column, and a calculated total sales column. The data is loaded from a power query that connects to our SQL server. The sections should also have subtotals for each month, so I figured a flat pivot table might do the trick here.
So far, so good.
The problem is that the calculated (sum/sigma) fields are always placed at the far right of the pivot and there doesn't seem to be an obvious way to "mix them up" with the other columns/data (ie row fields). I tried to fix this by setting the quantity sold and price columns as calculated value fields, but then they have their own sub-total (which I don't see how to turn off), so the whole thing looks ridiculous.
Is this possible with a pivot table or am I barking up the wrong tree?
Thanks
Alex
Bookmarks