cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VHosamane Frequent Visitor
Frequent Visitor

Calculation from a lookup table with default

I have two tables, an aggregated table(AggrTbl) and another table (Lkup) is a lookup table. They are separate now but can be joined by Staff Name. I need help in calculating the Cost.

 

AggrAndLkupTables.png

 

In the AggrTbl, I need to check if StaffName exists in Lkup table. If the StaffName exist, then i need to the corrosponding Rate. Then multiply Rate times AggrWklyHrs. If StaffName does not exist in Lkup table, then I have a default rate of 50. What formula should I use for Cost in the AggrTbl ?

1 ACCEPTED SOLUTION

Accepted Solutions
MoOv Regular Visitor
Regular Visitor

Re: Calculation from a lookup table with default

Try something like this:

Cost =

VAR varLookup =
    LOOKUPVALUE (

         Lkup[Rate];
         Lkup[StaffName]; AggrTbl[StaffName]

    )

RETURN
IF (
    varLookup <> BLANK();
    varLookup;
    50

)

 

Regards

2 REPLIES 2
MoOv Regular Visitor
Regular Visitor

Re: Calculation from a lookup table with default

Try something like this:

Cost =

VAR varLookup =
    LOOKUPVALUE (

         Lkup[Rate];
         Lkup[StaffName]; AggrTbl[StaffName]

    )

RETURN
IF (
    varLookup <> BLANK();
    varLookup;
    50

)

 

Regards

VHosamane Frequent Visitor
Frequent Visitor

Re: Calculation from a lookup table with default

Worked perfectly. Thank you so much.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 145 members 2,009 guests
Please welcome our newest community members: