Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have a table called Timesheet Reports, like this:
Name | Date | Week | Hours |
Clare | 10/02/2020 | 1 | 30 |
Clare | 17/02/2020 | 2 | 20 |
Clare | 24/02/2020 | 3 | 40 |
James | 10/02/2020 | 1 | 19 |
James | 17/02/2020 | 2 | 30 |
James | 24/02/2020 | 3 | 25 |
John | 10/02/2020 | 1 | 35 |
John | 17/02/2020 | 2 | 34 |
John | 24/02/2020 | 3 | 22 |
Kate | 10/02/2020 | 1 | 12 |
Kate | 17/02/2020 | 2 | 33 |
Kate | 24/02/2020 | 3 | 1.5 |
Lily | 10/02/2020 | 1 | 4.5 |
Lily | 17/02/2020 | 2 | 10 |
Lily | 24/02/2020 | 3 | 9 |
Sally | 10/02/2020 | 1 | 20.5 |
Sally | 17/02/2020 | 2 | 14 |
Sally | 24/02/2020 | 3 | 19 |
Note that the week is calculated using a look up from another table called Dates:
I want another column that calculates the percentage of total hours per week, i.e.:
E.g. for the last line hours is 19, the week number is 3, and sum of hours for week 3 in this table is 116.5.
So for that row we do 19/116.5 = 16.3%.
What's the best formula for this in Power BI?
Note that I have filters on the page for week number. So we only ever view 1 week at a time.
I've tried using this measure:
hi @Erf19
You may try to adjust measure Total Hours for Week Selected as below:
Total Hours for Week Selected = CALCULATE(SUM('Timesheet Reports'[Hours]),ALLSELECTED('Timesheet Reports'))
Then it should work well.
Regards,
Lin
sorry, this doesn't seem to work either
hi @Erf19
Please share your sample pbix file and expected output, it would be a great help.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490...
Regards,
Lin
Try to use
Total Hours for Week level = CALCULATE(SUM('Timesheet Reports'[Hours]),allexcept('Timesheet Reports'[Financial Week]))
refer:
https://community.powerbi.com/t5/Desktop/calculate-category-total-in-table-visualization/td-p/519340
https://community.powerbi.com/t5/Desktop/highest-value-by-category/td-p/428758
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
Hi, thanks for your response.
I just tried this. First got an error about number of arguments in 'allexcept' so I changed it to:
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |