Hello!
I am interested to compare the costs per item for different items, regarding their type. Type 0 is my main type or baseline for every item, so all other types of the product should be compared to this type.
So the result should be a pivot table, which should look like this:
see screenshot
Alternatively it could also be a table that looks like this:
| ITEM |
TYPE |
Costs |
Costs/Item |
Costs/Item Type 0 |
Diff Costs/Item |
| A |
0 |
500 |
25 |
25 |
0 |
| A |
1 |
900 |
30 |
25 |
5 |
| A |
2 |
1000 |
10 |
25 |
-15 |
| B |
0 |
300 |
10 |
10 |
0 |
| B |
1 |
16 |
8 |
10 |
-2 |
| B |
2 |
200 |
20 |
10 |
10 |
| C |
0 |
100 |
5 |
5 |
0 |
| C |
1 |
60 |
12 |
5 |
7 |
| C |
2 |
60 |
30 |
5 |
25 |
I tried to use set analysis, e.g. Costs/Item Type 0
sum({$<[TYPE]={'0'}>} [Costs])/sum({$<[TYPE]={'0'}>} [Amount])
, but this is not working. I just get a null value (-) for the columns under an other type, for type 0 it is working. When I set the $ to 1 in my set analysis, I am not able to filter by items anymore.
My formula for the Diff Costs/Item then would be:
(sum(Costs)/sum(Amount))-(sum({$<[TYPE]={'0'}>} [Costs])/sum({$<[TYPE]={'0'}>} [Amount]))
How do i get the formula right?
Thank you very much!
Best regards,
Daniel
Edit: I have attached the pivot table as screenshot, because it was not fully displayed here.
