Calculate % in a table with different denomitors

[Anonymous]

I have a table with data for 10 different locations string bins. Each location has a storage bin capacity

I want to :

Location (1) actuals bins / bin Capacity (1)

Location (2) actual bins / bin capacity (2)

etc

etc

I want the table to show:

Location        Actual Bins        Capacity       % Capacity

1                     4                       10                40%

2                      5                       15                33%

3                     10                       100              10%

Any help would be greatly appreciated. Thx

[Anonymous · Accepted solution]

HI @Anonymous ,

You can create a new table based on table 1 and add new column to lookup tbale2 values:

Table 2 =
ADDCOLUMNS (
    T1,
    "Capacity", LOOKUPVALUE ( T2[Capacity], T2[Location], T1[Location] )
)

Then you can use divide function to calculate the '%loading':

% Loading = DIVIDE([Actual],[Capacity])

Regards,

Xiaoxin Sheng

[Ashish_Mathur]

Hi,

What does your source data look like?  Share it here so that i can paste it in an Excel file.

[Anonymous]

ok thx

Query 1	Location	Actual
	1	5
	2	10
	3	15
	4	20
Query 2	Location	Capacity
	1	10
	2	20
	3	20
	4	50
Table	Location	Actual	Capacity	% Loading
	1	5	10	50%
	2	10	20	50%
	3	15	20	75%
	4	20	50	40%

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous · Accepted solution]

HI @Anonymous ,

You can create a new table based on table 1 and add new column to lookup tbale2 values:

Table 2 =
ADDCOLUMNS (
    T1,
    "Capacity", LOOKUPVALUE ( T2[Capacity], T2[Location], T1[Location] )
)

Then you can use divide function to calculate the '%loading':

% Loading = DIVIDE([Actual],[Capacity])

Regards,

Xiaoxin Sheng