I am trying to sum values that are on other tables onto a master table, but I can't seem to get it to work. It is essentially a Sumif function, but I can't seem to get Calculate and or SUMX to work. It always wants to return the fee for the entire related table (Revenue or Time).
Please see the below image. I am trying to sum Fees and Hours onto the Assignments table for the individual assignments. The Revenue and Time tables are linked to Assignments.
Solved! Go to Solution.
Best to use power query/query editor then bring the data through in one table.
Start with 'group by' function which is basically a sumif and do this for both the revenue table and the time table. This will produce one summed up value for each assignment. Then merge the time table into the revenue table, linking them by the assignment, select the hours colum to bring through to the revenue table. This will produce what you need 3 rows and 2 columns of data. Boom!
I feel that this one is asking the same question (I have a one to many relationship), but i havent been able to get it to work http://community.powerbi.com/t5/Desktop/Create-a-Calculated-Column-from-Different-Tables/m-p/60773#M...
Relationships: The tables are connected via an Assignment Key field.
Formula: HHG = CALCULATE(SUM('Assignment Schedule Split'[Revenue (USD)])).
- The fields on the left of HHG are from the Assignment table.
- The formula is on the assignments table