Forum Discussion

SandyBL's avatar
SandyBL
Advocate II
9 years ago
Solved

Lookup value based on value falling within range on other table

Looking to create the "Actual Rate" column in a table based on a rate card table that has the rules on which rate to use for each volume level.  So need help with the Actual Rate column.  I can do th...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    SandyBL

     

     

    ActualRate =
CALCULATE (
    VALUES ( Rates[Rate] ),
    FILTER (
        Rates,
        Rates[Min Volume] <= Volume[Actual Volume]
            && Rates[Max Volume] >= Volume[Actual Volume]
               && Rates[Account]= Volume[Account]
    )
)
SandyBL's avatar
SandyBL
Advocate II
9 years ago

Just realized its more complex than that

 

Table 1: Rare Card

Rate    Account        Min Volume      Max Volume

$5        1                          0                        10,000

$4        1                         10,001                11,000

$8        2                          0                        10,000

$7        2                         10,001                11,000

 

Table 2: Volume Delivered

Account    Actual Volume     Actual Rate

1                   10,500                  $4

 

 

So the rate card table has the same min and max volumes repeated for different accounts, so when using that formula it has mutliple Rates that fall within the same range and I get an error.  How do I filter to look at the specific account first before looking at the range?

Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

SandyBL

 

 

ActualRate =
CALCULATE (
    VALUES ( Rates[Rate] ),
    FILTER (
        Rates,
        Rates[Min Volume] <= Volume[Actual Volume]
            && Rates[Max Volume] >= Volume[Actual Volume]
           && Rates[Account]= Volume[Account]
    )
)
  • SandyBL's avatar
    SandyBL
    Advocate II
    9 years ago

    Thank you for your help