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.
Hi,
I am attemping to calculate the sum of allowed $ based on hours worked on a given day. I currently enter time on several rows since different chargecodes are involved.
7/1/2022 | 5 hours
7/1/2022 | 3 hours
7/1/2022 | 2 hours
Then i am calculating the sum based on day, this determines the allowed $. Since I work over 10hrs on 7/1/22, I should be allowed 32, instead its multiplying by 3 (# of rows). Displaying my allowed as 96.
Additionally, for the days where no time(hours) was entered, i am getting a blank. I would prefer to be a 0 rather than empty/blank.
My Current Allowed calculated column is:
Thank you for the assitance and time.
Solved! Go to Solution.
Hi @Coryanthony
What's happening here is that the calculated column makes a value for $allowed on every single row of data. As there are 3 rows for 7/1/22, naturally there are 3 repeated values of $allowed.
When $Allowed is dropped into a table, it is a number and will be automatically aggregated which is causing those values to Sum.
There are a few things you can do to change this:
1) Change the model so that there is a separate table dimension for employee & date
3) Change $Allocation to a measure rather than a calculated column
This could have performance implications, but the equivalent of your calculation above, as a measure would be:
Allowed_As_Measure =
SumX(SUMMARIZE(
Merge1,
Merge1[Employee ID],Merge1[Date],Merge1[Holiday],Merge1[Day of Week],
"DailyHrs",sum(Merge1[Hours])),
SWITCH(TRUE,
[DailyHrs]>=4 && [DailyHrs]<8 && Merge1[Holiday] = "Yes" ,12,
[DailyHrs]>=8 && Merge1[Holiday] = "Yes" ,32,
[DailyHrs]>=10 &&'Merge1'[Day of Week]<=4,20,
[DailyHrs]>=4 && [DailyHrs]<8 && 'Merge1'[Day of Week]>=5,12,
[DailyHrs]>=8 && 'Merge1'[Day of Week]>=5,32,
0
))
Was this solved? Anyone has thoughts on this ?
Hi @Coryanthony
What's happening here is that the calculated column makes a value for $allowed on every single row of data. As there are 3 rows for 7/1/22, naturally there are 3 repeated values of $allowed.
When $Allowed is dropped into a table, it is a number and will be automatically aggregated which is causing those values to Sum.
There are a few things you can do to change this:
1) Change the model so that there is a separate table dimension for employee & date
3) Change $Allocation to a measure rather than a calculated column
This could have performance implications, but the equivalent of your calculation above, as a measure would be:
Allowed_As_Measure =
SumX(SUMMARIZE(
Merge1,
Merge1[Employee ID],Merge1[Date],Merge1[Holiday],Merge1[Day of Week],
"DailyHrs",sum(Merge1[Hours])),
SWITCH(TRUE,
[DailyHrs]>=4 && [DailyHrs]<8 && Merge1[Holiday] = "Yes" ,12,
[DailyHrs]>=8 && Merge1[Holiday] = "Yes" ,32,
[DailyHrs]>=10 &&'Merge1'[Day of Week]<=4,20,
[DailyHrs]>=4 && [DailyHrs]<8 && 'Merge1'[Day of Week]>=5,12,
[DailyHrs]>=8 && 'Merge1'[Day of Week]>=5,32,
0
))
Hi @pi_eye
I love the measure! It is doing exactly what i've spent half of day attemping to figure out.
Follow up question if I may. On days an employee submits >1 expense, on a different report number, it is doubling the allowed amount. Please see snippet. would you happen to have any solution for this? I would like it to only display the allowed amount for that day (not based on amount of reports submitted.
On 11/22/2022, 2 reports were submitted; therefore, it is calculating $20 for both reports, rathan than only $20 for that day.
Additionally, it appears i am getting a blank rather than 0 on days time was not supposed.
Thank you again for your time, much appreciated :).
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |