cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## create measure to calculate % of total and allocation

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
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

## Re: create measure to calculate % of total and allocation

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support

## Re: create measure to calculate % of total and allocation

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### ‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors