Writing dynamic values in a table to a new calculated column

[ek2112]

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.

[ek2112]

Here is the source .pbix file, in case you want to look at it. Thanks again!

Testing.pbix

[Greg_Deckler]

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.

[ChandeepChhabra]

@ek2112

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

[ek2112]

@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!

Pbix file link

[ChandeepChhabra]

@ek2112

1. So you mean to say you would like to see the "Daily Cost of Employee" based on your date drill down. Something like..

• Daily view = 320 
• Weekly = 320 x 5
• Monthly = 320 x 40

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"?

[ek2112]

@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.

[ChandeepChhabra]

@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

[ek2112]

@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! 

[ChandeepChhabra]

@ek2112

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!

[ek2112]

@ChandeepChhabra  Thank you, tried but it is now messing up the daily cost:

[ChandeepChhabra · Accepted 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])))

[ek2112]

@ChandeepChhabra Perfect! Worked like a charm! Thank you for your time.

[ek2112]

@ChandeepChhabra If I may, here is the file I am trying to replicate  @Greg_Deckler's solution.

pbix file with index column and custom column

[Greg_Deckler]

@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)

[ek2112]

@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)

[Greg_Deckler]

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.