Multiply Hrs x Pay Rate

[frankee_gee]

How to multiply employee hours times pay rate. I want to avoid creating columns if I can w/ in Power Pivot.

I would like to create a Dax formula with in power pivot that will give me this:

ST Labor Cost:= STHrs x STR
OT Labor Cost:= OTHrs x OTR
DT Labor Cost: = DTHrs x DTR

Also:

Total Hrs:=STHrs + OTHrs + DTHrs
Total Labor Cost: = ST Labor Cost + OT Labor Cost + DT Labor Cost

Thank you for your time in advance,

Frankee

 

[scottsen]

It's going to depend on what your power pivot tables and relationships look like. Can you give us any info there?

 

[frankee_gee]

Hello Scott,

It all sits on the same table. the only other table at this point is a calendar table.

 

[scottsen]

Maybe paste a bit of the table?

Why the hate for adding columns to your table? How many rows are we talking?

As a calculated column, you are just talking =MyTable[Rate] * MyTable[Hours] or whatever.
As a measure, it should be something like... =SUMX(MyTable, MyTable[Rate] * MyTable[Hours])

 

[frankee_gee]

Thanks for the reply. Into this spread sheet I add about 8K rows a month. I'm up 38K rows at the moment. I did what you suggest before. Became to slow. every time I refreshed the data. My data and Spread sheet are in the same excel book. Is that good practice?

 

[scottsen]

Okay, so you you have an excel sheet, with ~40k rows. You import that into power pivot. Where do the 8k per month come from? Any way to get those "more directly" into power pivot?

I would probably build a separate workbook, but I don't think it's critical.

40k rows is pretty small by power pivot standards, surprised you are seeing any perf issues.

When you are talking about adding columns, do you mean to the original data, or adding a new calculated column in power pivot?

At any rate, the measure i wrote should give you a reasonable starting point for your calculations?

 

[frankee_gee]

Scottsen,

About 10K records per month currently @ 40K or so.
1. Receive a data dump at the end of each month
2. Massage the data...dates, text to columns, Format columns/Cells...numbers, currency, PO N WO numbers to Text.
3. Once it's clean I add to my master excel spread sheet.
4. Refresh All Data.

Feels like I have double the records in same book.
1st. Spread Sheet 40K rows
2nd. PowerPivot 40K rows.

If I dont change this now. At what point would you suggest I separate the two?

I dont have direct access to any database/tables. I just accept what I receive monthly and generate reports from that.

Adding Columns: Power Pivot. It can get out of hand real quick. If I can avoid it, I would.

And Yes, It did work for me: OTAmt: =SUMX(TRACK,TRACK[OTH]*TRACK[OTR]). I did this for ST, OT and DT.

I also used this to add of the amounts: LaborSpend:=[STAmt]+[OTAmt]+[DTAmt]
and this for the sum of all hours: SUM(TRACK[STH])+SUM(TRACK[OTH])+SUM(TRACK[DTH])

I saved some column space w/ in power pivot.


Thanks for the help,

Frankee.

 

[scottsen]

Cool, glad the formulas worked for you. Personally, I would be tempted to use SQL Express -- free and holds up to 10gb which is likely plenty.

The other option would be NOT to put data into a "master excel sheet"... and instead have 1 .csv file per table. Power Pivot then refreshes from the updated .csv files each month.