Hi
I have two separate queries in one workbook. One has the time (as a decimal) worked on a range of activities, each of which is identified by a unique ID in the format CWR0001, CWR0002, etc. The other query is a list of the CWRs with the hourly rate for each one (A dim table).
I've loaded both queries into the data model and created a link between them based on CWR ID, which the first table has many instances of and the second table only has one instance of each.
I'd like to use the data to create a pivot table in my workbook in the format:
CWR ID, Result of multiplying the two numbers.
I'm drawing a total blank on how to do this using either Power Query or Power Pivot, other than loading each query into the workbook as a table and doing a Vlookup to put the hourly rate in the first table, then using Excel to multiply them, then creating a pivot table.
I have Excel 2016, if that makes any difference.
Thoughts?
Thanks in advance for any help that you can offer!
Mardi
Hi Mardi,
The most efficent way is to do this with a measure in Power Pivot where the table containing the times is called CWR_Time and the Dim table containing rates is called CWR_Rates and the formula is:
=SUMX(CWR_Time, CWR_Time[Time] * RELATED(CWR_Rates[Rate]))
Mynda
So simple, so easy... when you know how! Thanks Mynda - I've learned so much from you already and today I've learned something else to add to my skills 🙂
