Dynamic Allocation of Fixed costs

[DSP VARMA]

The circled ones in the given screenshot are the fixed expenses per month which are divided equally by the days of the respective month.

These fixed expenses are further allocated to 4 (out of 10) main domains in a particular ratio.

For eg., the total personnel cost per day is 10,277.82. This cost is allocated to AE - 879.8, COM – 2,085, SA – 7,168, EG - 145 and for the rest it is 0.

I used the following formula for this

Personnel = if(HASONEFILTER(DOMAIN[Domain]),IF(CONTAINS(DOMAIN,DOMAIN[Domain],"AE"),879.8,IF(CONTAINS(DOMAIN,DOMAIN[Domain],"COM"),2085.26666666667,IF(CONTAINS(DOMAIN,DOMAIN[Domain],"SA"),7167.76666666667,IF(CONTAINS(DOMAIN,DOMAIN[Domain],"EG"),145,IF(CONTAINS(DOMAIN,DOMAIN[Domain],"NOMAD"),0,IF(CONTAINS(DOMAIN,DOMAIN[Domain],"QA"),0,IF(CONTAINS(DOMAIN,DOMAIN[Domain],"BH"),0,IF(CONTAINS(DOMAIN,DOMAIN[Domain],"OM"),0,IF(CONTAINS(DOMAIN,DOMAIN[Domain],"CO"),0,IF(CONTAINS(DOMAIN,DOMAIN[Domain],"JO"),0)))))))))),10277.83)

Same wise for the rest of the fixed expenses in the given screenshot.

In each fixed expense formula, I enter domain allocation expenses manually.

1. Can I make the arrow values dynamic?

• Eg. If I change the Total Column value all the numbers in the formula should change

	Total	AE	COM	SA	EG
		8.56%​	20.29%	69.74%​	1.41%​
Personnel	10,278​	880	2,085	7,168	145
General Administration	119​	10	24	83	2
IT Expenses	1221​	105	248	852	17
Rent and Lease	1273​	109	258	888	18

2. The total Column value at the last is showing the same as the remaining columns. Instead, it should show the aggregate for 31 or 30 days as per the days of the month.

 

[Fluff]

Duplicate to: Dynamic Allocation of Values

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.