Power BI Filtering based on value from unrelated table

[TGaldieri]

Hi,

I have an issue with what supposed to be a simple formula.

I have 5 tables:

Location Origin Fact Table

Location Destination Fact Table

Date Fact Table
Sales Dimension Table

Contract Sales Dimension Table

In my organisation we sell products to our clients and deliver these products from Origin to Destination. We have clients that signed a contract that promised us they will purchase X volume that will originate from point A and will be delivered to point B during a contract validity date.

What I need to find out is what is the overall contract volume fulfilment percentage is. Here are table examples (fictional data).

Both Sales and Contract sales table related many to 1 to date table.
They are also related many to 1 to origin and destination table respectfully.

What I need to measure is overall contract fulfilment %.

Contract sales table contains numbers that we were promised customer will purchase. Sales table contains actual purchase numbers while Contract sales table contains promised volume. Please note, same customer can purchase items outside of the contract (fields in grey in Sales table, no match in Contract Sales table).

I need to calculate contract fulfilment %.

If I was to write a measue Fulfilment % = DIVIDE(SUM(Sales Qty),SUM(Contract Sales Qty)) then my result would be (10+39+5+2+20)/(15+43+2) = 76/60= 126.67%

This result is incorrect because it is counting all Sales Qty without checking if this origin and destination of the sale are registered in Sales contract table.

I need a formula that would give me the outcome (10+39+5)/(15+43+2) = 54/60 = 90%

I tried multiple CALCULATE and FILTER functions but they don't work due to the nature of relations in the table.

Thanks for your help!

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.