locked
Compute rate per hour RRS feed

  • Question

  • Hi,

    I have 2 simple tables.  The first table is named Employees and second one is named hourly_rate.  I have created a relationship from the Grade column of the Employees table to the Grade column of the hourly_rate table.  I now want to create a Pivot Table dragging Names from the Employees table and rate per hour from the hourly_rate table.  I wrote the following calculated field formula but the result I get is 21 for all employees.

    =SUM(rate_per_hour[Rate per hour])

    Also, in the Pivot Table field list section, I get a message saying that "Relationships between tables may be needed".  There is already a relationship between tables so why do I get that message.  Please note that I do not want to create spare column in the Employees table in the Data model to first bring over the rate per hour from the hourly rate table.  I am looking at directly writing a calculated field formula for the same.

    Please help.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Sunday, September 3, 2017 3:14 AM

Answers

  • All relationships are one to many. Your grade table is on the one side of the relationship. The employee table is in the many side. Filters automatically propagate from the one side to the many side - not the other way around. As a result, employees will not Filter rate automatically. This formula will work. =calculate(sum(rate[rate]),EmployeeTableName) But this is very complex dax. If you want to read about it, read here https://exceleratorbi.com.au/many-many-relationships-dax-explained/

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    • Marked as answer by Ashish Mathur Monday, September 4, 2017 12:04 AM
    Sunday, September 3, 2017 7:15 AM
    Answerer

All replies

  • All relationships are one to many. Your grade table is on the one side of the relationship. The employee table is in the many side. Filters automatically propagate from the one side to the many side - not the other way around. As a result, employees will not Filter rate automatically. This formula will work. =calculate(sum(rate[rate]),EmployeeTableName) But this is very complex dax. If you want to read about it, read here https://exceleratorbi.com.au/many-many-relationships-dax-explained/

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    • Marked as answer by Ashish Mathur Monday, September 4, 2017 12:04 AM
    Sunday, September 3, 2017 7:15 AM
    Answerer
  • Thank you for sharing this article.

    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Monday, September 4, 2017 12:05 AM