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

[kristien]

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

[Gysbert_Wassenaar · Accepted solution]

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.

[kristien]

Excellent Gysbert! That did the job. Thx!

Kristien