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...
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)