If column A Table 1 matches column B Table 2 then return column B table 1

[rmblack92]

Hi,

I'm relatively new to Power BI and need to build a report that shows which suppliers are on contract in order to compare the total spend versus contracted spend as a value & a percentage.

Example tables: 

Table 1 

Supplier Number	Total Spend
A123	£98
B123	£94
C123	£100
D123	£88

Table 2

Supplier Number

A123

B123

D123

What I want to show 

Supplier Number	Total Spend	Contracted Spend
A123	£98	£98
B123	£94	£94
C123	£100
D123	£88	£88
Total	£380	£280

Using the sum of each column I will create a Card that shows the percentage of contracted spend so in this example the contract spend would be 73.68%

Please help! 

[v-xiaotang]

Hi @rmblack92 

BTW, you can use HASONEVALUE() to get the expected result,

Contracted Spend = IF(HASONEVALUE(Table1[Supplier Number]),CALCULATE(MIN(Table1[Total Spend (£)]),FILTER(Table1,RELATED(Table2[Supplier Number])<>BLANK())),[Contracted Spend Total])

See sample file attached bellow.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[AllisonKennedy · Accepted solution]

@rmblack92  It looks like you have only some of your suppliers in the DimSupplier table - defined as 'Contracted Spend'. Do you have the non-contracted suppliers in a table somewhere too? It would be handy to have this info in one table. I've created a sample report, but I'm using the SupplierID from the Spend table which is not generally a good idea. 

You can create a measure like this; 

Total Spend Contracted =

SUMX(

FILTER(Spend, RELATED( Suppliers_Contracted[Supplier Number]) <> BLANK())

, Spend[Total Spend]

)

See attached file below signature.