Forum Discussion
Helper IIVlookup Measure: Calculate ALL Granularity
Hi,
I have a problem.
I have measure which calculates the % of work billable on a project. (Billable Utilisation)
This has to be done as a measure, as I am using a date slicer and needs to be relative to the date.
I also have a Timecard Table, which contains: Employee Name, Projects Worked on and Days logged against this project.
An employee can work on multiple projects.
I need to equivalent of the lookup values for the Project chargeable measure, so I can relate the measure result for each project to the timecard table, without being affected by granularity/filters etc.
Would a SUMX with ALL() Function work?....
Please Help!
6 Replies
Brianoreilly,
What table does the date field come from? You can create a new table using DAX below.Table = SUMMARIZE(Project,Project[Project Name],"Utilisation",[Billable Utilisation])
Then create required columns in the Timecard table.Project chargeable Utilisation = LOOKUPVALUE('Table'[Utilisation],'Table'[Project Name],Timecard[Project Name])Billable days = Timecard[Project chargeable Utilisation]*Timecard[Days Logged]
Regards,
Lydia
BrianoreillyHi Lydia, Anonymous
The method above does not take into account the Date Table I am using.
I use a seperate Date table to calculate the utilisation.
The summarize table, is suming all days, and not relative to the date filter (slicer).
Also for some reason, the lookup value, only seems to allow a measure in the "search value".
Any help appreciated :)
Thanks,
Brian.
Brianoreilly,
Could you please share the excel file contains all the table data to me? Based on your description, you want to "relate the measure result for each project to the timecard table, without being affected by granularity/filters etc".
Regards,
Lydia
