At first, I did a normal pivot table and added a calculated field (Price Per Unit) to calculate unit price (=Revenue/Qty) but then I discovered that calculated fields in normal pivot table will not subtotal correctly Price Per Unit under Krogers (should be 203.48), similar issue with Meijers. Saw someone mention being able to do it with data model and calculated columns.
So, I inserted the data into data model and used Power Pivot as you can see in the pic. But same issue when it came to subtotal using calculated columns' measures (=Revenue/Qty).
Any help would be greatly appreciated.
Thanks!
