Calculate Value with a measured quantity based on different types

[awarriorpoet]

Need help figuring out how to create a measure and show in a visual based on multiplying a measure (Shortage) with a lookup of three different options based on "SIZE".
Use Case: We sold more containers than we have fruit. How much more fruit can I sell based on the container sizes that were sold...with three options for each size.

Values for OPTION E,A,P below will be based on the Shortage Qty multiplied by the value based on the size category (Table 2)

I want to create a visual table or matrix that looks like this:  

DESIRED RESULT

CUSTOMER	SIZE	SHORTAGE	OPTION E	OPTION A.	OPTION P
ACME	SMALL	0	$                   -	$                   -	$                   -
ACME	MED	3	$ 12.00	$  15.00	$ 18.00
ACME	LARGE	3	$ 18.00	$  21.00	$  24.00
ZORRO	SMALL	1	$ 2.00	$ 3.00	$  4.00
ZORRO	MED	5	$ 20.00	$ 25.00	$  30.00
ZORRO	LARGE	6	$ 36.00	$ 42.00	$  48.00

I am currently using Three Measures to calculate the shortage qty.  **(I would like to make this one if possible)**

FOOD QTY =CALCULATE(SUM(Table1[Item Quantity]),CNDATA[Type]="FOOD")

HARD QTY = CALCULATE(SUM(Table1[Item Quantity]),CNDATA[Type]="CONTAINER")

SHORTAGE = Table1[HARD QTY]-TBL1[FOOD QTY]

Current Measure Results

FOOD	7
CONTAINER	13
SHORTAGE	6

Sales data looks like this, but there will is more than one customer.

Table1

Customer Name	Product ID	Item Quantity	Type	Product Size	Price Net $	Option E	Option A	Option P
ACME	BANANA	1	FOOD	SMALL	$1.50	$2.00	$3.00	$4.00
ACME	APPLE	1	FOOD	SMALL	$1.50	$2.00	$3.00	$4.00
ACME	WATERMELON	1	FOOD	SMALL	$2.25	$2.00	$3.00	$4.00
ACME	BANANA	1	FOOD	SMALL	$1.50	$2.00	$3.00	$4.00
ACME	CEREAL BOX	1	FOOD	MED	$1.50	$4.00	$5.00	$6.00
ACME	CEREAL BOX	1	FOOD	MED	$1.50	$4.00	$5.00	$6.00
ACME	WATERMELON	1	FOOD	LARGE	$2.25	$6.00	$7.00	$8.00
ACME	BAG	1	CONTAINER	SMALL	$1.50	$2.00	$3.00	$4.00
ACME	BAG	1	CONTAINER	SMALL	$1.50	$2.00	$3.00	$4.00
ACME	BAG	1	CONTAINER	SMALL	$1.50	$2.00	$3.00	$4.00
ACME	BAG	1	CONTAINER	SMALL	$1.50	$2.00	$3.00	$4.00
ACME	MED BASKET	1	CONTAINER	MED	$2.50	$4.00	$5.00	$6.00
ACME	MED BASKET	1	CONTAINER	MED	$2.50	$4.00	$5.00	$6.00
ACME	MED BASKET	1	CONTAINER	MED	$2.50	$4.00	$5.00	$6.00
ACME	MED BASKET	1	CONTAINER	MED	$2.50	$4.00	$5.00	$6.00
ACME	MED BASKET	1	CONTAINER	MED	$2.50	$4.00	$5.00	$6.00
ACME	LARGE BASKET	1	CONTAINER	LARGE	$3.50	$6.00	$7.00	$8.00
ACME	LARGE BASKET	1	CONTAINER	LARGE	$3.50	$6.00	$7.00	$8.00
ACME	LARGE BASKET	1	CONTAINER	LARGE	$3.50	$6.00	$7.00	$8.00
ACME	LARGE BASKET	1	CONTAINER	LARGE	$3.50	$6.00	$7.00	$8.00

This is the size / option table. (This does not currently exist but I may need to create one)

Table2

Product Size	Option E	Option A	Option P
SMALL	$2.00	$3.00	$4.00
MED	$4.00	$5.00	$6.00
LARGE	$6.00	$7.00	$8.00

[123abc]

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.

[awarriorpoet]

I have the  Matrix working as expected.

I would like to add card visuals that show just the totals.  When I use the "measure" I created outside of the table - it calculates an incorrect value.

How can I create a card visual that shows just the total.

Formulat would be like:
Option E Total = (shortage type A * cost type A for Option E) + (shortage type B * cost type B for Option E) + (...)

See "table 2" above that has the family and option costs.

[123abc]

To create a card visual that shows the total for each option, you can create separate measures for each option and then sum them up for the card visual. Assuming you have measures like Option_E, Option_A, and Option_P, you can create total measures for each option and then use them in the card visual.

Here's an example for Option E:

Option_E_Total =
CALCULATE(
SUMX(
VALUES(Table1[Customer Name], Table1[Product Size]),
[Option_E]
)
)

This measure calculates the sum of the Option_E measure for each unique combination of "Customer Name" and "Product Size." You can create similar measures for Option A and Option P:

Option_A_Total =
CALCULATE(
SUMX(
VALUES(Table1[Customer Name], Table1[Product Size]),
[Option_A]
)
)

Option_P_Total =
CALCULATE(
SUMX(
VALUES(Table1[Customer Name], Table1[Product Size]),
[Option_P]
)
)

Now, you can use these total measures in your card visuals:

• Drag the Option_E_Total measure to a card visual to display the total for Option E.
• Similarly, use Option_A_Total and Option_P_Total for Option A and Option P.

This approach ensures that you're correctly summing up the individual option values for each unique combination of "Customer Name" and "Product Size" in your dataset. Adjust the column and table names based on your actual data model.

[awarriorpoet]

Thank you as always for your quick response.  I am getting an error when using the VALUES() command you recommended.  It will only accept 1 argument.  Not sure how to fix it to get intended outcome.

[awarriorpoet]

Thank you, this is exactly what I was trying to do.  I simplified the shortage measure and created three measures to be used in the visuals, however, I am getting an error on the "option" measurements.

Here is what I did:

1. I created a table (below) using the "Enter Data" function

2. I created a relationship to the sales data table to this table in a Many:1 relationship via the "FAMILY" field.

3. Created the option Measures in the sales table (Table 1)

Error: "The column 'tblSWPricing[OPTION_E]' either doesn't exist or doesn't have a relationship to any table available in the current context."

FAMILY	OPTION_E	OPTION_A	OPTION_P
SIZE A	10.26	18.46	21.96
SIZE B	10.87	21.75	31.03
SIZE C	12.08	29	41.35
SIZE D	10.87	21.75	31.03
SIZE E	12.08	29	41.35
SIZE F	2.26	3.4	4.88
SIZE G	6.8	8.44	11.59
SIZE H	11.28	18.46	21.96

[awarriorpoet]

I created a measure that seems to be working.  I could not get RELATE() to work.

OPTION_P_$ = Table1[Shortage] *

      LASTNONBLANK(

            SELECTCOLUMNS(

                 RELATEDTABLE(tblSWPricing),

                        "OPTION_P",tblSWPricing[OPTION_P]),

        1)