Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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 :).
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
72 | |
62 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
62 | |
59 | |
56 |