Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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])))
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?
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.
Since the daily cost remains the same throughout the column. You can write a simple MAX measure
Daily Cost of Emp = MAX(Data[Daily Cost])
Here is the snapshot of the result.
You can download the pbix here
Hope this helps
@ChandeepChhabra many thanks. This worked to a point where all the resources are employees and the "Daily Cost of Emp" worked. I tried to modify this to have "Contractors" in the mix (as in the "Employee Type" column in the "Data (2) table of the pbix file), but the results are not quite what I expected. Basically, if it is employee, the daily cost should be 320 for each day, 320 * 40 for each month, 320* 5 for each week . For contractor, it varies based on the hours spent.
Thank you once again!
1. So you mean to say you would like to see the "Daily Cost of Employee" based on your date drill down. Something like..
If so, can you share a more real time data, as of now there are only single start of month dates in your data
2. Currently I can see 2 cost numbers (160, 80) for the same employee in the month of Nov & Dec. Which one would you like to use in case the emplpyee type is "contractor"?
@ChandeepChhabra for 1. Yes, that is correct. For "Full-time" employee type, the daily cost is constant, 8*40 = 320 regardless of whether they work less than or more than 8 hours.
For 2. I am using mm/dd/yy format for the month of January. So in this case employee "DN" is a contractor with a rate of $40/h. He worked on two tickets on 1/11/2018, 54 and 21, for 4 and 2 hours respectively. So "Daily cost" will be the sum of (40*4)+(40*2) = 240 for 1/11/2018. What throws me off is the calculation to get the "Daily Cost" for the "Full-time" employee constant regardless of the daily hours.
Hope I am able to explain it better, my apologies for the confusion. I am using the same data file I shared with you and also the one I was using with @Greg_Deckler. Let me know if you need more data points. Thanks for looking.
@ek2112 Please try this measure
Daily Cost = IF(ISBLANK(CALCULATE(MAX(Test[Daily Emp Cost]),Test[Employee Type]="Full-time")),SUMX(Test,Test[Daily Emp Cost]),CALCULATE(MAX(Test[Daily Emp Cost]),Test[Employee Type]="Full-time"))
Don't worry about the dates, mine is by default dd-mm-yyyy format
Let me know if this works. PBIX Download
@ChandeepChhabra progress! Except there is a minor hiccup - the daily cost for the "full-time" employee is coming up fine (see fig 1) however when I drill up to month level, it comes up as 320 (fig 2). Instead, it should be = 320+320 = 640 as JB worked for two days in the month of Jan. I have included the pbix file with a new measure 'Nett' but otherwise it is the same copy. Thanks again!
Try changing
Daily Cost measure
= IF(ISBLANK(CALCULATE(SUM(Test[Daily Emp Cost]),Test[Employee Type]="Full-time")),SUMX(Test,Test[Daily Emp Cost]),CALCULATE(SUM(Test[Daily Emp Cost]),Test[Employee Type]="Full-time"))
I have just substituted MAX with SUM in the calculate. This should work now!
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])))
@ChandeepChhabra If I may, here is the file I am trying to replicate @Greg_Deckler's solution.
@ek2112- If you really want what you were originally asking for, go into the Query Editor and add an Index column.
Then you could create a calculated column like this:
Column = VAR minIndex = MINX(FILTER(ALL('#Tickets'),'#Tickets'[Name]=EARLIER('#Tickets'[Name]) && '#Tickets'[Work date]=EARLIER('#Tickets'[Work date])),[Index]) RETURN IF([Index]=minIndex,320,0)
@Greg_Deckler many thanks, this worked, however when I tried to replicate it for type "Contractor", I get a circular reference error when tweaked the formula to add up the daily cost in the first cell for each instance of the "Contractor". Appreciate your help!
Original formula: Cost to Company = VAR minIndex = MINX(FILTER(ALL('Test'),'Test'[Name]=EARLIER('Test'[Name]) && 'Test'[Work date]=EARLIER('Test'[Work date])),[Index]) RETURN IF([Index]=minIndex,320,0)
Tweaked formula: Cost to Company = VAR minIndex = MINX(FILTER(ALL('Test'),'Test'[Name]=EARLIER('Test'[Name]) && 'Test'[Work date]=EARLIER('Test'[Work date])),[Index]) RETURN IF([Index]=minIndex,IF(Test[Employee Type]="Full-time",320,[Daily Cost]*[Work Time]),0)
So, what I would do would be to create a measure for Daily Cost. Essentially, something like this in psuedo-code:
Employee Cost = IF('Table'[EmployeeType]="Part",SUM('Table'[Daily Cost]),320)
You should be able to use this measure in your visualization instead of your Daily Cost column.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |