Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have two tables, 'Work Details' and 'Rate' and linked by 'Name' column. Each employee has their own hourly rates in the 'Rate' table's 'Rate' column.
I have additional columns in the Rate table - Rate 2, Rate 3 and Rate 4 - with different rates for each employee as shown below.
Is there a way I can display a slicer with just 'Rate', 'Rate 2' , 'Rate 3' and 'Rate 4' in the drop down and trigger calculations in related columns of the 'Work Details' table (like 'Daily Emp Cost' column) that currently points to 'Rate' column of the 'Rate' table, as a What if scenario? I tried with HASONEVALUE function, but could not wrap my head around it. Thanks for your help.
Rate table:
Work details table:
Can you provide a visual of your expected end result? I know how to do this, I just need to see what you want.
Thanks for looking into this. Here is how I envision the expected result (I mocked this up in Excel):
Very helpful - if that's fabricated or anonymous data, it'd be useful if you could upload it to Dropbox/OneDrive/etc. and share the xlsx directly.
Hi @ek2112,
The calcualted column can't be changed by the slicer. I would suggest you create a measure instead. Please also refer to the attached demo.
1. Pivot the Rate table,
2. Create a measure.
Daily Emp Cost New = VAR dailyhours = CALCULATE ( SUM ( 'Work Details'[Work Time] ), ALLEXCEPT ( 'Work Details', 'Work Details'[Name], 'Work Details'[Work date] ) ) VAR rate_selected = IF ( HASONEVALUE ( Rate[Rates] ), LOOKUPVALUE ( Rate[Value], Rate[Name], MIN ( 'Work Details'[Name] ), Rate[Rates], MIN ( Rate[Rates] ) ), LOOKUPVALUE ( Rate[Value], Rate[Name], MIN ( 'Work Details'[Name] ), Rate[Rates], "Rate" ) ) RETURN IF ( SELECTEDVALUE ( 'Work Details'[Weekend] ) = FALSE (), IF ( dailyhours < 8, IF ( MIN ( [Employee Type] ) = "Contractor", MIN ( [Work Time] ) * rate_selected, 8 * rate_selected ) ) )
Best Regards,
Dale
@v-jiascu-msft great thank you. The measure currently has repeated entries of for Full-time employees. Is it possible to get just one entry of Daily empocost per day per full-time employee and have the rest as zero entries in the Daily Emp Cost New measure column? It is fine as it is for Contractor employee type. I have illustrated it for 1/04/18 entries in the following screenshot.
Hi @ek2112,
Try this new measure please.
Daily Emp Cost New = VAR minIndex = CALCULATE ( MIN ( 'Work Details'[Index] ), ALLEXCEPT ( 'Work Details', 'Work Details'[Name], 'Work Details'[Work date] ), 'Work Details'[Employee Type] = "Full-time" ) VAR dailyhours = CALCULATE ( SUM ( 'Work Details'[Work Time] ), ALLEXCEPT ( 'Work Details', 'Work Details'[Name], 'Work Details'[Work date] ) ) VAR rate_selected = IF ( HASONEVALUE ( Rate[Rates] ), LOOKUPVALUE ( Rate[Value], Rate[Name], MIN ( 'Work Details'[Name] ), Rate[Rates], MIN ( Rate[Rates] ) ), LOOKUPVALUE ( Rate[Value], Rate[Name], MIN ( 'Work Details'[Name] ), Rate[Rates], "Rate" ) ) RETURN IF ( SELECTEDVALUE ( 'Work Details'[Weekend] ) = FALSE (), IF ( dailyhours < 8, IF ( MIN ( [Employee Type] ) = "Contractor", MIN ( [Work Time] ) * rate_selected, IF ( MIN ( 'Work Details'[Index] ) = minIndex, 8 * rate_selected, 0 ) ) ) )
Best Regards,
Dale
@v-jiascu-msft that worked for day level. If I try to sum at the month/quarter/year level, this returns empty especially whe there is date hierarchy
@v-jiascu-msft just noticed that the measure is not working if summed at the monthly level, ie if I use the date hierarchy for work date.
Hi @ek2112,
There aren't any [work time] or "daily hours" existed in monthly level.
1. How can we handle it? Sum the daily level values?
2. How about other levels? Such as, Year.
Best Regards,
Dale
@v-jiascu-msft Thanks for your response.
1. Yes, I am trying to sum the [work time] at monthly/yearly level.
2. Same as 1. above.
Can the application not take the daily level [work time] hours and do summation?
Hi @ek2112,
In order to keep the formula short, I would suggest you create a new measure which will invoke the old one. Please give it a try.
New = SUMX ( SUMMARIZE ( 'Work Details', 'Work Details'[Name], 'Work Details'[Employee Type], 'Work Details'[Project Name], 'Work Details'[Work date], "DEC", [Daily Emp Cost New] ), [DEC] )
Best Regards,
Dale
Hello @v-jiascu-msft
Thanks again, It worked if there are few rows in 'Work Details' table, but when I added more rows for another employee, the behaviour is weird - the measure is coming up as empty.
Hi @ek2112,
Please comfirm if the blue part (<=) is good. That's the cause.
Daily Emp Cost New = VAR minIndex = CALCULATE ( MIN ( 'Work Details'[Index] ), ALLEXCEPT ( 'Work Details', 'Work Details'[Name], 'Work Details'[Work date] ), 'Work Details'[Employee Type] = "Full-time" ) VAR dailyhours = CALCULATE ( SUM ( 'Work Details'[Work Time] ), ALLEXCEPT ( 'Work Details', 'Work Details'[Name], 'Work Details'[Work date] ) ) VAR rate_selected = IF ( HASONEVALUE ( Rate[Rates] ), LOOKUPVALUE ( Rate[Value], Rate[Name], MIN ( 'Work Details'[Name] ), Rate[Rates], MIN ( Rate[Rates] ) ), LOOKUPVALUE ( Rate[Value], Rate[Name], MIN ( 'Work Details'[Name] ), Rate[Rates], "Rate" ) ) RETURN IF ( SELECTEDVALUE ( 'Work Details'[Weekend] ) = FALSE (), IF ( dailyhours <= 8, // Just change from < to <= IF ( MIN ( [Employee Type] ) = "Contractor", MIN ( [Work Time] ) * rate_selected, IF ( MIN ( 'Work Details'[Index] ) = minIndex, 8 * rate_selected, 0 ) ) ) )
Best Regards,
Dale
@v-jiascu-msft I tried again after changing the formula, but the "Contractor" calculation is not coming right for some - for example, as per this screenshot, for AA, it should be 31.50 * 60 = 1890, but coming up as 450. Rest of the three came up fine. Here is the pbix. Thanks again.
@v-jiascu-msft after quite a bit of effort, I tweaked the formula a bit and got it working. Thanks for your help.
Just one final question - is there a way to get the actual costs based on different rates for different time periods? E.g. For one resource, if the resource has $30/hr for Jan, cost = [work time] * 30, else cost = [work time] * rate_selected. I tried DATEDIFF but could not quite get it. Thanks.
Daily Emp Cost New = VAR minIndex = CALCULATE ( MIN ( 'Work Details'[Index] ), ALLEXCEPT ( 'Work Details', 'Work Details'[Name], 'Work Details'[Work date] ), 'Work Details'[Employee Type] = "Full-time" ) VAR dailyhours = CALCULATE ( SUM ( 'Work Details'[Work Time] ), ALLEXCEPT ( 'Work Details', 'Work Details'[Name], 'Work Details'[Work date] ) ) VAR rate_selected = IF ( HASONEVALUE ( Rate[Rates] ), LOOKUPVALUE ( Rate[Value], Rate[Name], MIN ( 'Work Details'[Name] ), Rate[Rates], MIN ( Rate[Rates] ) ), LOOKUPVALUE ( Rate[Value], Rate[Name], MIN ( 'Work Details'[Name] ), Rate[Rates], "Rate" ) ) RETURN IF (MIN ( [Employee Type] ) = "Full-time", IF ( MIN ( 'Work Details'[Index] ) = minIndex, IF (SELECTEDVALUE ( 'Work Details'[Weekend] ) = FALSE (), 8* rate_selected, IF(SELECTEDVALUE ( 'Work Details'[Weekend] ) = TRUE(), 0, MIN ([Work Time]) * rate_selected)),IF(MIN ( [Employee Type] ) = "Contractor", MIN ( [Work Time] ) * rate_selected,0)),MIN ( [Work Time] ) * rate_selected)
Hi @ek2112,
So glad you solve it. Maybe you can set another VAR to get the $30/hr then you can apply it in the formula. That will depend on where we can get the $30/hr.
Best Regards,
Dale
@v-jiascu-msft thanks will try this out. I am basically hardcoding this within the formula? If you can give me something by changing the formula, that would be awesome. Thanks.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |