Forum Discussion
Helper ICalculate Value with a measured quantity based on different types
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 |
6 Replies
- 123abc
Community Champion
Create a Size/Option Table (Table2): You've already provided this table. Ensure it is in your data model.
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])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.
awarriorpoetThank 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)
- 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.
