PowerPivot DAX Reallocation of Site Amounts to Other Sites

[Xandria619]

Good day,

I have the following table and I need to reallocate the amounts for Site 1 to all the other Sites, based on the percentage of total amount that each of the other sites have against the total revenue excluding Site 1.

To illustrate, the data, which comes from a pivottable:

Sum of Amount

Month

Site

1​

2​

3​

4​

5​

6​

7​

8​

9​

10​

11​

12​

Grand Total

SITE 1

33 883​

9 564​

21 055​

21 731​

43 755​

13 287​

12 819​

35 867​

16 694​

3 763​

45 983​

26 133​

284 534​

SITE 2

(21 116)​

(30 214)​

(18 715)​

(3 273)​

(26 089)​

(6 737)​

(36 945)​

(29 609)​

(26 268)​

(35 254)​

(17 115)​

(15 238)​

(266 573)​

SITE 3

(46 709)​

(27 794)​

(46 482)​

(708)​

(19 366)​

(2 936)​

(34 239)​

(21 613)​

(36 995)​

(46 355)​

(32 613)​

(35 268)​

(351 078)​

SITE 4

(17 819)​

(24 651)​

(14 654)​

(34 763)​

(24 357)​

(16 919)​

(1 392)​

(26 256)​

(24 475)​

(39 141)​

(45 732)​

(16 760)​

(286 919)​

SITE 5

(11 903)​

(12 280)​

(55 275)​

(21 448)​

(23 771)​

(22 358)​

(10 100)​

(13 881)​

(27 524)​

(19 174)​

(18 738)​

(21 009)​

(257 461)​

SITE 6

(21 579)​

(4 758)​

(35 585)​

(34 655)​

(8 568)​

(23 742)​

(37 704)​

(20 018)​

(6 530)​

(23 039)​

(15 695)​

(17 950)​

(249 823)​

SITE 7

(21 122)​

(21 544)​

(17 142)​

(30 255)​

(7 912)​

(13 694)​

(33 734)​

(15 921)​

(34 762)​

(20 192)​

(8 762)​

(39 034)​

(264 074)​

SITE 8

(35 925)​

(25 909)​

(34 964)​

(34 275)​

(18 123)​

(48 773)​

(28 330)​

(40 315)​

(24 744)​

(14 157)​

(48 381)​

(4 163)​

(358 059)​

SITE 9

(14 597)​

(39 129)​

(23 554)​

(36 540)​

(30 207)​

(34 389)​

(21 601)​

(55 243)​

(28 957)​

(12 273)​

(14 473)​

(29 250)​

(340 213)​

Grand Total

(156 887)​

(176 715)​

(225 316)​

(174 186)​

(114 638)​

(156 261)​

(191 226)​

(186 989)​

(193 561)​

(205 822)​

(155 526)​

(152 539)​

(2 089 666)​

These are the tables I need to calculate and show in Excel:

1) The overheads to be allocated:

Site/Month

1

2​

3​

4​

5​

6​

7​

8​

9​

10​

11​

12​

Total

SITE 1

33 883​

9 564​

21 055​

21 731​

43 755​

13 287​

12 819​

35 867​

16 694​

3 763​

45 983​

26 133​

284 534​

2) Total Amount Excluding Site 1:

Total Amount

190 770​

186 279​

246 371​

195 917​

158 393​

169 548​

204 045​

222 856​

210 255​

209 585​

201 509​

178 672​

2 374 200​

3) Overhead allocation

Site

1

2

3

4

5

6

7

8

9

10

11

12

Total

SITE 1

(33 883)​

(9 564)​

(21 055)​

(21 731)​

(43 755)​

(13 287)​

(12 819)​

(35 867)​

(16 694)​

(3 763)​

(45 983)​

(26 133)​

(284 534)​

SITE 2

3 750​

1 551​

1 599​

363​

7 207​

528​

2 321​

4 765​

2 086​

633​

3 906​

2 229​

30 938​

SITE 3

8 296​

1 427​

3 972​

79​

5 350​

230​

2 151​

3 478​

2 937​

832​

7 442​

5 158​

41 353​

SITE 4

3 165​

1 266​

1 252​

3 856​

6 728​

1 326​

87​

4 226​

1 943​

703​

10 436​

2 451​

37 439​

SITE 5

2 114​

630​

4 724​

2 379​

6 567​

1 752​

635​

2 234​

2 185​

344​

4 276​

3 073​

30 913​

SITE 6

3 833​

244​

3 041​

3 844​

2 367​

1 861​

2 369​

3 222​

518​

414​

3 581​

2 625​

27 919​

SITE 7

3 752​

1 106​

1 465​

3 356​

2 186​

1 073​

2 119​

2 562​

2 760​

363​

1 999​

5 709​

28 450​

SITE 8

6 381​

1 330​

2 988​

3 802​

5 006​

3 822​

1 780​

6 488​

1 965​

254​

11 040​

609​

45 465​

SITE 9

2 593​

2 009​

2 013​

4 053​

8 344​

2 695​

1 357​

8 891​

2 299​

220​

3 303​

4 278​

42 055​

4) Total after overhead allocation

Site/Month

1​

2​

3​

4​

5​

6​

7​

8​

9​

10​

11​

12​

Total

SITE 1

-​

-​

-​

-​

-​

-​

-​

-​

-​

-​

-​

-​

-​

SITE 2

(17 366)​

(28 663)​

(17 116)​

(2 910)​

(18 882)​

(6 209)​

(34 624)​

(24 844)​

(24 182)​

(34 621)​

(13 209)​

(13 009)​

(235 635)​

SITE 3

(38 413)​

(26 367)​

(42 510)​

(629)​

(14 016)​

(2 706)​

(32 088)​

(18 135)​

(34 058)​

(45 523)​

(25 171)​

(30 110)​

(309 725)​

SITE 4

(14 654)​

(23 385)​

(13 402)​

(30 907)​

(17 629)​

(15 593)​

(1 305)​

(22 030)​

(22 532)​

(38 438)​

(35 296)​

(14 309)​

(249 480)​

SITE 5

(9 789)​

(11 650)​

(50 551)​

(19 069)​

(17 204)​

(20 606)​

(9 465)​

(11 647)​

(25 339)​

(18 830)​

(14 462)​

(17 936)​

(226 548)​

SITE 6

(17 746)​

(4 514)​

(32 544)​

(30 811)​

(6 201)​

(21 881)​

(35 335)​

(16 796)​

(6 012)​

(22 625)​

(12 114)​

(15 325)​

(221 904)​

SITE 7

(17 370)​

(20 438)​

(15 677)​

(26 899)​

(5 726)​

(12 621)​

(31 615)​

(13 359)​

(32 002)​

(19 829)​

(6 763)​

(33 325)​

(235 624)​

SITE 8

(29 544)​

(24 579)​

(31 976)​

(30 473)​

(13 117)​

(44 951)​

(26 550)​

(33 827)​

(22 779)​

(13 903)​

(37 341)​

(3 554)​

(312 594)​

SITE 9

(12 004)​

(37 120)​

(21 541)​

(32 487)​

(21 863)​

(31 694)​

(20 244)​

(46 352)​

(26 658)​

(12 053)​

(11 170)​

(24 972)​

(298 158)​

(156 887)​

(176 715)​

(225 316)​

(174 186)​

(114 638)​

(156 261)​

(191 226)​

(186 989)​

(193 561)​

(205 822)​

(155 526)​

(152 539)​

(2 089 666)​

I am unable to get DAX to calculate the grand totals on the Overhead Allocation and on the Total after Overhead allocation correctly.

Thank you very much, any advice / help will be greatly appreciated.

Kind regards