Pivot table with inter-row set analysis calculation

cata_pli — Sat, 16 Nov 2024 05:45:10 GMT

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:

If(Item=3, Sum({<Item={1,2}, View=>}total <Day> Amount), Sum(Amount))

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.

I attach sample app.

Thank you for your time !

topic Pivot table with inter-row set analysis calculation in App Development

Pivot table with inter-row set analysis calculation