Forum Discussion

MojoGene's avatar
MojoGene
Post Patron
10 years ago
Solved

LOOKUPVALUE help

I am attempting to determine timekeeper net profit by subtracting the timekeeper's costs from the value of the timekeeper's work.

 

There is a table, 'TimeTable', that contains columns for [Timekeeper], [Hours], [Billing Rate], etc.

 

In the TimeTable, there is a column [Hourly Value] that is the product of [Hours]*[Billing Rate].

 

I also have a measure called [YTD Value]: YTD Value = TOTALYTD(SUM(TimeTable[Hourly Value]), Table_BasicCalendarUS[DateKey], "12/31")

 

Annual costs for each timekeeper are set forth in a simple lookup table called 'Costs', which has only two columns, one colum for [Timekeeper] and one column for total annual costs, [Annual Cost].

 

I want to determine YTD costs in this situation by doing a lookup on the Costs table for [Annual Cost] on each Timekeeper and then multiply the annual costs by the year fraction. (I have a column in DimDate called [Calendar Year Fraction].)

 

So, my measure for profit looks like this:

 

YTD Profit = MAX(TimeTable[YTD Value]) - LOOKUPVALUE(Costs[TotalComp ], Costs[Timekeeper],TimeTable[Timekeeper]) * DimDate[Calendar Year Fraction]

 

But this returns the following error: "Column 'YTD Value' in table 'TimeTable' cannot be found or may not be used in this expression."

 

How would I putting this together?

 

Thanks.

Need Help
  • Sean's avatar
    Sean
    10 years ago

    MojoGene These are the Measures I used ....

     

    YTD Value = TOTALYTD(SUM(TimeTable[Hourly Value]), CalendarTable[Date])

YTD Cost = SUM(Costs[Total Comp])*YEARFRAC(STARTOFYEAR(CalendarTable[Date]), TODAY(),3)

YTD P/L = [YTD Value] - [YTD Cost]

YTD P/L % = DIVIDE([YTD P/L], [YTD Cost], 0)

     

    Here's the sample I created and the result... Hope this helps!

     

7 Replies

  • mike_honey's avatar
    mike_honey
    Memorable Member
    10 years ago

    I think it's best to avoid LOOKUPVALUE in Measures - it works better in Calculated Columns.

     

    I would add a Relationship between TimeTable and Costs (on TimeKeeper) and between TimeTable and DimDate (assuming TimeTable has a Date/DateKey column).

     

    Then the measure would look something like:

     

    YTD Profit = SUM(TimeTable[YTD Value]) - ( SUM(Costs[TotalComp ] * SUM ( DimDate[Calendar Year Fraction] ) )

    • MojoGene's avatar
      MojoGene
      Post Patron
      10 years ago

      Mike:

       

      Thanks for taking a stab at this. Unfortunately, I am still running into a brick wall.

       

      The relationships you suggested already existed. 

       

      The measure you suggested returned the following error: "Column 'TotalComp' in table 'Costs' cannot be found or may not be used in this expression."

       

      Any suggestions on the solution here would be appreciated.

      • Nhallquist's avatar
        Nhallquist
        Helper V
        10 years ago

        mike_honey is right, you want to avoid LOOKUPs.  In the Relationships screen of PBI, can you verify that the relationship between Cost Table and Time Table is active?  Also, you should look to make sure that there is a Many to One (Many in Time, One in Cost) Relationship created.  If the relationship is not active, or it is a Many to Many, you will have issues.  If the Cost table has multiple entries for each TimeKeeper, you should be able to edit the data query to only bring the max value for each TimeKeeper.

         

        Let us know the results.

         

        Nate