I am working with a pivot table. I have Category and Quarter for dimensions. I have items that fit into more than one category and the way data is linked actually creates multiple rows of the items....one item row per category with a field showing true or false that it is part of that category. For the expression I am trying to get an average cost per category. So, sum the cost for each item and divide by the total number of items that are in that category. Sounds simple enough but because there are multiples rows of the same item, it's summing every item record instead of every unique item. So if there are 5 records for Item A and the cost is 2.00 and 5 records of Item B for 3.00, them the sum should be 5.00 but instead I am getting 25.00.
AGGR(SUM(Price), ItemIDD) <-- this doesn't work. Ideas?
The accurate calculation of avg cost is summarizing the cost for all the lines and dividing it by the non-distinct count of all included lines:
sum(Cost)/count(Cost)
You don't really need to use AGGR for this calculation. If you really want to calculate cost per Item and then average them out, you can do something like this:
Here is a breakdown of what a table box of the data would look like.
CategoryA Item1 2.00 True
CategoryA Item2 3.00 True
CategoryB Item1 2.00 True
CategoryB Item2 3.00 False
CategoryC Item1 2.00 True
CategoryC Item2 3.00 True
CategoryD Item1 2.00 False
CategoryD Item2 3.00 True
CategoryE Item1 2.00 True
CategoryE Item2 3.00 False
Result desired (pivot table including a quarter date dimension but will assume all same quarter for example):
CategoryA 2.50
CategoryB 2.00
CategoryC 2.50
CategoryD 3.00
CategoryE 2.00
Currently, I am getting this result (sum(cost) / count(distinct item)):
CategoryA 12.5
If I break down the expression to test it out. The distinct count work but the sum portion does not. The sum is looking at every instance of the item instead of only the instance tied to that category. For categoryA, it should be 5/2 = 2.50. Instead I'm getting 25/2=12.5
