Forum Discussion
Advocate IIWriting dynamic values in a table to a new calculated column
I am a Power BI newbie here. Below is a table of timelogs from time entry system. The objective is to compute their profitability based on the projects they work on. "Daily Cost" column contains the cost to the company (8h x $40/h = $320). So even if the employee logs less than 8h/day, cost to the company is the same, i.e, $320. SOW Rate column provides the rate per hour by project. I am trying to use CALCULATE and FILTER functions to get just the first value of "Daily Cost" column (instead of the way it repeats right now), write it to a separate column (lets call it "Distinct Daily Cost"), grouped by name "Name" and "Work date". Table 2 is the end goal.
Table 1 source:
| Name | Work date | Issue Number | Work Time | Billed Time | Daily Cost | Weekend | Project Name | SOW Rate | Revenue |
| JB | 01/04/18 | Ticket-1 | 1 | 1 | 320 | False | P1 | 140 | 140 |
| JB | 01/04/18 | Ticket-2 | 2 | 2 | 320 | False | P1 | 140 | 280 |
| JB | 01/04/18 | Ticket-24 | 1 | 1 | 320 | False | P1 | 140 | 140 |
| JB | 01/04/18 | Ticket-19 | 2 | 2 | 320 | False | P1 | 140 | 280 |
| JB | 01/04/18 | Ticket-14 | 0.5 | 0.5 | 320 | False | P2 | 200 | 100 |
| JB | 01/05/18 | Ticket-2 | 1 | 1 | 320 | False | P1 | 200 | 200 |
| JB | 01/05/18 | Ticket-4 | 0.5 | 0.5 | 320 | False | P2 | 200 | 100 |
| JB | 01/05/18 | TIcket-6 | 0.25 | 0.25 | 320 | False | P2 | 200 | 50 |
| JB | 01/05/18 | Ticket-44 | 0.5 | 0.5 | 320 | False | P3 | 100 | 50 |
| JB | 01/05/18 | Ticket-32 | 0.5 | 0 | 320 | False | P3 | 100 | 0 |
| DN | 01/11/18 | Ticket-54 | 4 | 0 | 320 | False | P3 | 100 | 0 |
| DN | 01/11/18 | Ticket-21 | 2 | 2 | 320 | False | P4 | 200 | 400 |
| DN | 01/12/18 | Ticket-11 | 1 | 1 | 320 | False | P4 | 200 | 200 |
| DN | 01/12/18 | Ticket-4 | 1 | 1 | 320 | False | P2 | 200 | 200 |
Basically, write the first value of the "Daily cost" column into "Distinct Daily Cost" column and keep other values as zero, as shown below:
I could get this by COUNTIFS and IF in excel but for the life of me, could not get this to work in DAX. Any help is greatly appreciated. Thank you in advance.
Sorry the measure was incorrect
I modified the measure and it works correctly now
Daily Cost = IF(ISBLANK(CALCULATE(SUM(Test[Daily Emp Cost]),Test[Employee Type]="Full-time")),SUMX(Test,Test[Daily Emp Cost]),SUMX(VALUES(Test[Work date]),MAX(Test[Daily Emp Cost])))
17 Replies
ek2112- Greg_Deckler
Community Champion
Will take a look although, can you provide me with ultimately what you are trying to do after you get Table2, there may be a better way to achieve what you ultimately want to do in Power BI and DAX. Is there ultimately some calculation you want to perform?
- ek2112
Thank you for your time Greg_Deckler. What I ultimately want to do is summarize the net profitability by employee on a daily/weekly basis in a table as below (mocked up from the same data as in the shared pbix file):
I was able to figure out for the contract employees, as the daily cost to the company (or the hourly rate times the hours worked) is straight forward as the hours are variable in case of contract employees. But for full-time employees the hours are not variable (fixed at 8h, no overtime) and the associated daily cost calculation should be the same even if the "Work time" sums up to less than 8h per day. So in case of example above, JB a full-time employee, logged 6.5 hours but cost is calculated on a 8h per day basis.
