Hi,
I am trying to create an easy way to see the average item sales rate. I want to take the total dollars sold / total quantity sold so I can get this average rate. I created a calculation with the following formula.
SUM(Item rate * shipped quantity) / SUM(shipped quantity)
When I add this into the pivot table, I assume it is summing the value because it is a very large number. I wanted to try average but there is no option to change the aggregate. I wasn't sure the difference between a calculation and a key metric so I create a key metric version called Key Metric Average Rate and I now get the menu option but all the options are grayed out and not selectable. Any idea how I can get my pivot table to show the SKU and its average sale rate? Instead of 91,965 I am expecting 163.3. I can get the value in a table where each sales order is its own line but when I try to wrap it up into a pivot table I am getting issues. Thanks.