Calculate Value with a measured quantity based on different types

1. Create a Size/Option Table (Table2): You've already provided this table. Ensure it is in your data model.

2. Create a measure for Shortage: Instead of having three separate measures, you can create one measure for Shortage.

SHORTAGE =
CALCULATE(
SUM(Table1[Item Quantity]),
Table1[Type] = "CONTAINER"
) - CALCULATE(
SUM(Table1[Item Quantity]),
Table1[Type] = "FOOD"
)

Create measures for Options E, A, and P:

OPTION_E = [SHORTAGE] * RELATED(Table2[Option E])
OPTION_A = [SHORTAGE] * RELATED(Table2[Option A])
OPTION_P = [SHORTAGE] * RELATED(Table2[Option P])

1. Build the visual table/matrix: Use a table or matrix visual in Power BI or your preferred tool. Place the following fields in the appropriate areas:

   • Rows: Customer Name, Product Size
   • Values: Shortage, OPTION E, OPTION A, OPTION P

   This will give you the desired result table with Shortage and options calculated based on the size and values from Table2.

Here's a summary of the measures:

SHORTAGE =
CALCULATE(
SUM(Table1[Item Quantity]),
Table1[Type] = "CONTAINER"
) - CALCULATE(
SUM(Table1[Item Quantity]),
Table1[Type] = "FOOD"
)

OPTION_E = [SHORTAGE] * RELATED(Table2[Option E])
OPTION_A = [SHORTAGE] * RELATED(Table2[Option A])
OPTION_P = [SHORTAGE] * RELATED(Table2[Option P])

Make sure that the relationships between the tables are set up correctly, and the calculations should work as expected.