Do a Cost Allocation in set Analysis

[matthewlau120]

Dear all,

I would like to exclude the others category in the screenshot and  evenly allocated its value into other categories, for example: 32000 should divide 5 and allocate into other categories. Would anyone feel free to help?

its set expression:Sum(Amount)-sum( {<NewType={"Others"}>}Amount) 

[amrinders]

Hi Matthew,

Create a variable in which you have to calculate sum of amount for only Others and divide it by distinct  count of New Type Field (Excluding Others). Then in your pivot/table you have to exclude 'Others' in your Dimension and in Measure you can write Sum({<NewType-={'Others'}>}Amount)+ $(vOthers).

Regards,

Amrinder

[marcus_sommer]

Maybe something like this might be helpful in your case:

sum({<NewType -= {'Others'}>} Amount) +
   (sum({1<NewType = {'Others'}>} Amount) *
        (sum({<NewType -= {'Others'}>} Amount) / sum({<NewType -= {'Others'}>} TOTAL Amount)))

- Marcus