Calculated Tables - how to add a measure column?

[Anonymous]

Hello! I'm working with employee time data.  I have an HR dataset with their salary for the year thus far, and a very large data set of every hour every person has logged.  I have their unique identifier (employee ID) and I've built quite a cool dashboard.

We're looking to get labor costs, so I calculate everyone's hourly rate = YTDSalary / working hours (it varies widely, isn't 40/week, and everyone is salary)

Hourly rate is a measure.  Working hours is a measure.  But they dont LIVE anywhere. They are only in the filters, you know?

I need to have power BI populate a calculated table. I was able to do that and make the first column = value(employee ID), but I'm getting no luck on the second column pulling in the Hourly Rate measure. Is that because that measure is built on another measure? Is it because I can't use DAX in the data view for some reason?

Any ideas are welcome!  Obviously as people log hours, their rate changes so definitely need this to be a dynamic measure.  I need it in a table because then in future tabs we have hours for specific projects being multiplied by that persons hourly rate.

[Anonymous]

FYI, when I just have it insert the measure, it gives me the sum of all of my people's hourly rates, not that one persons hourly rate.

[mahoneypat]

You can use a measure in a calculated column, but the evalution context is different so it is not surprising you are seeing unexpected results.  Why do you need the new table?  If you plan to export, you can do that from a table visual and get a csv.  If you do need a column expression, please share your measure and your table with some mock data so a specific column expression can be proposed.

Regards,

Pat

[Anonymous]

Thank you for reading and responding! You're right, I don't NEED a measure table. 

Here are the three tables I have, plus the measures I have built.

Measures that are currently functioning great:

Working hours = calculate(sum('FinanceTable'[hours]), 'PGID Table'[Time Type] = "working")

Hourly rate = divide(sum('HRTable'[YTD Salary]), [Working Hours], 0)

TMF Hours = calculate(sum('FinanceTable'[hours]), 'PGID Table'[Time Type] = "TMF")

BUT, now I need those hourly rates to be static somehow. I have another tab where I need to show a visualization with the rows: Project , Hours, Labor Cost

Therefore, I built this measure but it doesn't really work dynamically.  

Client Labor Cost = [Hourly Rate] * [TMF Hours]

Since we are only calculating the TMF type hours in this instance.

I need the hourly rate to be static somehow for each employee, it's like I need a dynamic table I can make relationships with OR a better written measure?  Let me know if any of thise makes sense!

[v-janeyg-msft · Accepted solution]

Hi， @Anonymous 

It’s my pleasure to answer for you.

According to your description,I think you can create a calculated column to calculate the desired result.

Like this:

Hourlyrate =
VAR a =
    SUMX (
        FILTER (
            ALL ( FinanceTable ),
            RELATED ( 'PGID Table'[Time Type] ) = "working"
                && [EmployeeID] = EARLIER ( HRTable[Employee] )
        ),
        [Hours]
    )
RETURN
    DIVIDE ( [YTDSalary], a )

 

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.