Calculated measure in Pivot table?

[devarasu07]

Hi All,

Is it possible to create calculated measure in Pivot table - dimension tab (i'can't put this calculation in expression tab due to my requirement report template restriction).

used below expression in Dimension,

=aggr(SUM(SoldQty),ArticleNo,Batch)

Thanks, Deva

[adamdavi3s]

I'm not sure this is possible.

I was hoping you might be able to get away with faking it using the totals label but it doesn't work because you're limited to the cell width of your dimension so you can't 'shift' the total under the correct column

[marcus_sommer]

You mean you want a total for your calculated dimension within the total-row of Material? I don't believe that this will be possible. A workaround for this might be to include this total within the calculated dimension itself, maybe something like this:

aggr(SUM(SoldQty),ArticleNo,Batch) & ' of ' & aggr(SUM(SoldQty),ArticleNo) & ' in total'

- Marcus

[devarasu07]

Hi marcus_sommer‌

Thanks for the workaround method, i've tried and it's working only when no dimension kept in pivot table column position. please refer to attached sample mock. kindly advise. Thanks

!

[marcus_sommer]

The expression for the calculated dimension must be look like:

= aggr(SUM(TotalNetPrice),ArticleNo,Batch) & ' of ' &

   aggr(NODISTINCT SUM(total <Batch, SKUTheme> TotalNetPrice),Batch, SKUTheme) &

   ' in total'

The suggestion from Adam is also very interesting but they will only work if you want to display a global total within because this label-field don't respect the dimensions within the chart.

- Marcus

[marcus_sommer]

In general there would be another possible approach by adding 'TOTAL' fieldvalue(s) within the field ArticleNo and then reacting per if-checks if there is a real ArticleNo or this dummy-value like this one:

if(ArticleNo = 'TOTAL', exp1, exp2)

by removing the partial sums for this field and formatting these rows with attribute expressions. But it will be also only a workaround and cost performance, makes it more complex and need some efforts to implement it.

- Marcus