Forum Discussion

awarriorpoet's avatar
awarriorpoet
Icon for Helper I rankHelper I
2 years ago

Calculate 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 contain...
123abc's avatar
123abc
Icon for Community Champion rankCommunity Champion
2 years ago

  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's avatar
    awarriorpoet
    Icon for Helper I rankHelper I
    2 years ago

    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 A10.2618.4621.96
    SIZE B10.8721.7531.03
    SIZE C12.082941.35
    SIZE D10.8721.7531.03
    SIZE E12.082941.35
    SIZE F2.263.44.88
    SIZE G6.88.4411.59
    SIZE H11.2818.4621.96

     

     

    • awarriorpoet's avatar
      awarriorpoet
      Icon for Helper I rankHelper I
      2 years ago

      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's avatar
    awarriorpoet
    Icon for Helper I rankHelper I
    2 years ago

    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's avatar
      123abc
      Icon for Community Champion rankCommunity Champion
      2 years ago

      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's avatar
        awarriorpoet
        Icon for Helper I rankHelper I
        2 years ago

        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.