topic Do a Cost Allocation in set Analysis in Management & Governance

Do a Cost Allocation in set Analysis

matthewlau120 — Thu, 14 Mar 2019 09:34:15 GMT

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)

Re: Do a Cost Allocation in set Analysis

amrinders — Thu, 14 Mar 2019 10:06:49 GMT

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

Re: Do a Cost Allocation in set Analysis

marcus_sommer — Thu, 14 Mar 2019 13:30:16 GMT

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