Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Excellent Gysbert! That did the job. Thx!
Kristien