topic Re: calculation with measure from other row-dimension-value but only for column dimension in App Development

calculation with measure from other row-dimension-value but only for column dimension

kristien — Wed, 09 May 2018 07:52:31 GMT

Hi,

I have a pivottable with Categories on the rows and months in the columns showing two measures:

the first measure is normal: sum([_Actual Amount]))

the second measure is the sum([_Actual Amount])) divided by the sum(_actual_amount) referring to one particular category in one of the rows.

I did this with following function:

num(sum( [_Actual Amount]) / Sum({$<[Category]={'xxx''}>} total [_Actual Amount]),'#0%')

This works fine as long as I have only one month selected for the columns.

If I select more months in the columns, the sum(total [_Actual Amount])) make a sum of all months instead of taking the value of the month (column) of the measure to be shown. How can I refer to the proper Column?

Any Ideas?

thx, Kristien

Re: calculation with measure from other row-dimension-value but only for column dimension

Gysbert_Wassenaar — Wed, 09 May 2018 08:13:20 GMT

Change the total qualifier so the totals are calculated per month:

num(sum( [_Actual Amount]) / Sum({$<[Category]={'xxx''}>} total <MyMonthDimension> [_Actual Amount]),'#0%')

Replace MyMonthDimension with the name of your month dimension.

Re: calculation with measure from other row-dimension-value but only for column dimension

kristien — Wed, 09 May 2018 08:17:56 GMT

Excellent Gysbert! That did the job. Thx!

Kristien