Pivot table with inter-row set analysis calculation
Hello, I have this input data:
Day
Item
Amount
View
d1
1
1
1
d1
2
1
1
d1
3
2
d2
1
1
1
d2
2
1
1
d2
3
2
I want a pivot table with Item and Day as Dimensions and a measure where for Item=3, I get the sum of Amount for Item 1 and 2, and for Item 1 and 2, normal Sum(Amount). I am using this formula:
I am using View=, because I want to be able to filter what I see in the table (that is to see only Item 3 when I apply a filter (2) on View, but still get the values from item 1 and 2.
The formula works when I don't apply filter on View=2, but when I do apply this selection on View filter I still see in the table the rows for Item 1 and 2 with 0 values, even though, I thought the filter View=2 would exclude them. I know I am using View= in formula, but that is only for Item 3, not the first two rows.
Why are this rows still there? Is there a way to see only Item 3 when I filter View=2 ?
Item
Day
d1
d2
1
0
0
2
0
0
3
2
2
I cannot use above(), or Supress 0 values. It has to be Set Analysis because in the real data model I will not know how many rows above to go, and I need to see zeroes.