Forum Discussion

artfulmunkeey's avatar
artfulmunkeey
Helper I
5 years ago
Solved

Help with LOOKUP?

Hi all, I am relatively new and struggling with what I believe should be quite straightforward, but no end of tutorials seem to be helping me as I get various syntax errors 😕   I have two tables: ...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi artfulmunkeey 

     

    Believe the best option is to create two dimension tables a project code and a calendar. Then make relationship between these two tables and the other two:

    Now create the following measures:

    HourBooked = SUM('Hours Booked'[HoursBooked])

HoursPlanned = SUM(HourPlanned[PlannedHours])

RemainingHours = [HoursPlanned] - [HourBooked]

     

    Now if you use the calendar and the projects table in your visualizations alongside with your dimension tables you can create the calculations in any way needed:

     

    Check PBIX file attach.

MFelix's avatar
MFelix
Super User
5 years ago

Hi artfulmunkeey 

 

Believe the best option is to create two dimension tables a project code and a calendar. Then make relationship between these two tables and the other two:

Now create the following measures:

HourBooked = SUM('Hours Booked'[HoursBooked])

HoursPlanned = SUM(HourPlanned[PlannedHours])

RemainingHours = [HoursPlanned] - [HourBooked]

 

Now if you use the calendar and the projects table in your visualizations alongside with your dimension tables you can create the calculations in any way needed:

 

Check PBIX file attach.

artfulmunkeey's avatar
artfulmunkeey
Helper I
5 years ago

Excellent, many thanks for your help!