Hi,
I'll explain my example since it'll be difficult to make sense without your data.
- Create a new file
- Go to the Script Editor and paste this code:
LOAD * INLINE [
Country, Sales
Canada, 456321
USA, 761234
Mexico, 231476
Ecuador, 982356
Colombia, 123212
Brasil, 453287
Uruguay, 679243
Chile, 543211
UK, 447231
];
- Reload the app.
- Create a pivot table with Country as first dimension
- Create a calculated dimension as second dimension with the following code.
=ValueList('Group1','Group2','Group3')
- Create the first expression with the following code:
Pick(Match(ValueList('Group1','Group2','Group3'),'Group1','Group2','Group3')
,sum(Sales)
)
- Create a second expression with this code:
Pick(Match(ValueList('Group1','Group2','Group3'),'Group1','Group2','Group3')
,0
,sum( {$<Country={'Canada','USA','Mexico'}>} Sales)
)
- Create a third expression with the next code:
Pick(Match(ValueList('Group1','Group2','Group3'),'Group1','Group2','Group3')
,0
,0
,sum( {$<Country={'Chile','Colombia','Ecuador'}>} Sales)
)
- Expand all dimensions and drag the second (calculated) dimension so that appears above the expressions line.
- Review the differences for each expression to understand why each one shows different results.
Hope this helps!
