Forum Discussion

YavuzDuran's avatar
YavuzDuran
Icon for Helper III rankHelper III
4 years ago
Solved

Vlookup - returning a Corresponding Value from Parameter Table

Hi All, 

 

I have a parameter table as shown below:

 

And all need is to match the calculated cash down % per Location for a year/month with the above cash % limits and return the corresponding $Com (Column H). 

I excel it is way easier, in Power BI I tried such a solution below

Is there any simpler way that you can suggest to me 

 

Thank you

 

 

 

 

 

 

Location Manager - Cash Down % Commission =
if(SalesCommission[Cash Down % - Calculated]<calculate(sum('Parameters-LocationManager'[Cash %]),filter('Parameters-LocationManager','Parameters-LocationManager'[Cash% Index]=1),filter('Parameters-LocationManager','Parameters-LocationManager'[Year-Month-Branch]=SalesCommission[Year-Month-Branch])),0,
if(SalesCommission[Cash Down % - Calculated]<calculate(sum('Parameters-LocationManager'[Cash %]),filter('Parameters-LocationManager','Parameters-LocationManager'[Cash% Index]=2),filter('Parameters-LocationManager','Parameters-LocationManager'[Year-Month-Branch]=SalesCommission[Year-Month-Branch])),calculate(sum('Parameters-LocationManager'[$ Com]),filter('Parameters-LocationManager','Parameters-LocationManager'[Cash% Index]=1),filter('Parameters-LocationManager','Parameters-LocationManager'[Year-Month-Branch]=SalesCommission[Year-Month-Branch])),
if(SalesCommission[Cash Down % - Calculated]<calculate(sum('Parameters-LocationManager'[Cash %]),filter('Parameters-LocationManager','Parameters-LocationManager'[Cash% Index]=3),filter('Parameters-LocationManager','Parameters-LocationManager'[Year-Month-Branch]=SalesCommission[Year-Month-Branch])),calculate(sum('Parameters-LocationManager'[$ Com]),filter('Parameters-LocationManager','Parameters-LocationManager'[Cash% Index]=2),filter('Parameters-LocationManager','Parameters-LocationManager'[Year-Month-Branch]=SalesCommission[Year-Month-Branch])),
if(SalesCommission[Cash Down % - Calculated]<calculate(sum('Parameters-LocationManager'[Cash %]),filter('Parameters-LocationManager','Parameters-LocationManager'[Cash% Index]=4),filter('Parameters-LocationManager','Parameters-LocationManager'[Year-Month-Branch]=SalesCommission[Year-Month-Branch])),calculate(sum('Parameters-LocationManager'[$ Com]),filter('Parameters-LocationManager','Parameters-LocationManager'[Cash% Index]=3),filter('Parameters-LocationManager','Parameters-LocationManager'[Year-Month-Branch]=SalesCommission[Year-Month-Branch])),
if(SalesCommission[Cash Down % - Calculated]<calculate(sum('Parameters-LocationManager'[Cash %]),filter('Parameters-LocationManager','Parameters-LocationManager'[Cash% Index]=5),filter('Parameters-LocationManager','Parameters-LocationManager'[Year-Month-Branch]=SalesCommission[Year-Month-Branch])),calculate(sum('Parameters-LocationManager'[$ Com]),filter('Parameters-LocationManager','Parameters-LocationManager'[Cash% Index]=4),filter('Parameters-LocationManager','Parameters-LocationManager'[Year-Month-Branch]=SalesCommission[Year-Month-Branch])),
calculate(sum('Parameters-LocationManager'[$ Com]),filter('Parameters-LocationManager','Parameters-LocationManager'[Cash% Index]=5),filter('Parameters-LocationManager','Parameters-LocationManager'[Year-Month-Branch]=SalesCommission[Year-Month-Branch])))))))
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi YavuzDuran,

    I modify your formula and tried to simplify these expressions, you can try to use the below formula if it helps:

    Location Manager - Cash Down % Commission =
VAR filtered =
    FILTER (
        'Parameters-LocationManager',
        'Parameters-LocationManager'[Year-Month-Branch] = SalesCommission[Year-Month-Branch]
    )
VAR currIndex = 'Parameters-LocationManager'[Cash% Index]
VAR currCashDown = SalesCommission[Cash Down % - Calculated]
VAR _cashIndex1 =
    CALCULATE (
        SUM ( 'Parameters-LocationManager'[Cash %] ),
        FILTER ( filtered, [Cash% Index] = 1 )
    )
RETURN
    IF (
        currIndex = 1,
        IF ( currCashDown < _cashIndex1, 0 ),
        IF (
            currCashDown
                < CALCULATE (
                    SUM ( 'Parameters-LocationManager'[Cash %] ),
                    FILTER ( filtered, [Cash% Index] = currIndex )
                ),
            CALCULATE (
                SUM ( 'Parameters-LocationManager'[$ Com] ),
                FILTER ( filtered, [Cash% Index] = currIndex - 1 )
            ),
            CALCULATE (
                SUM ( 'Parameters-LocationManager'[$ Com] ),
                FILTER ( filtered, 'Parameters-LocationManager'[Cash% Index] = 5 )
            )
        )
    )

    Regards,

    Xiaoxin Sheng

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    YavuzDuran Generally you would use LOOKUPVALUE or MAXX(FILTER(...),...). 

     

    If you stay with your current solution, I highly recommend SWITCH(TRUE(),...) vs nested IF statements.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi YavuzDuran,

    I modify your formula and tried to simplify these expressions, you can try to use the below formula if it helps:

    Location Manager - Cash Down % Commission =
VAR filtered =
    FILTER (
        'Parameters-LocationManager',
        'Parameters-LocationManager'[Year-Month-Branch] = SalesCommission[Year-Month-Branch]
    )
VAR currIndex = 'Parameters-LocationManager'[Cash% Index]
VAR currCashDown = SalesCommission[Cash Down % - Calculated]
VAR _cashIndex1 =
    CALCULATE (
        SUM ( 'Parameters-LocationManager'[Cash %] ),
        FILTER ( filtered, [Cash% Index] = 1 )
    )
RETURN
    IF (
        currIndex = 1,
        IF ( currCashDown < _cashIndex1, 0 ),
        IF (
            currCashDown
                < CALCULATE (
                    SUM ( 'Parameters-LocationManager'[Cash %] ),
                    FILTER ( filtered, [Cash% Index] = currIndex )
                ),
            CALCULATE (
                SUM ( 'Parameters-LocationManager'[$ Com] ),
                FILTER ( filtered, [Cash% Index] = currIndex - 1 )
            ),
            CALCULATE (
                SUM ( 'Parameters-LocationManager'[$ Com] ),
                FILTER ( filtered, 'Parameters-LocationManager'[Cash% Index] = 5 )
            )
        )
    )

    Regards,

    Xiaoxin Sheng