create measure to calculate % of total and allocation

[BQIAN]

Guys can anyone help please.

I have made a simple example to show what I am trying to achieve. Table 1 has cost in cost centre A where it is posted to and table B has usage/allocaiton key, both tables are separate queires uploaded into Power BI. Tabel 3 is a visual (table visual) I would like to achieve and I have tried to create 2 measures using SELECTALL to create TOTAL measure first and then DIVIDE to do the % but for some reasons when I add the new measure to new column it still breaks down by individual cost centre and hence % for each cost centre becomes 100%. Can anyone help me out please?

Source tables:

Table A

Cost Centre	$
Cost Centre A	1000

Table B

Cost Centre	Allocation Key
Cost Centre B	500
Cost Centre C	300
Cost Centre D	200
Cost Centre E	50

Table Visual

Cost Centre	Allocation Key	Total	%	Allocation
Cost Centre B	500	1050	48%	476
Cost Centre C	300	1050	29%	286
Cost Centre D	200	1050	19%	190
Cost Centre E	50	1050	5%	48
Total	1050	1050	100%	1,000

[v-lili6-msft · Accepted solution]

HI, @BQIAN

After my research, you could try these formulas as below:

Total = CALCULATE(SUM(TableB[Allocation Key]),ALLSELECTED(TableB))

% = DIVIDE(CALCULATE(SUM(TableB[Allocation Key])),[Total])

Allocation = CALCULATE(SUM(TableA[$]))*[%]

Result:

Best Regards,

Lin