Percent of Total for Week (calculations not giving expected answer)

[Erf19]

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:

Financial Week = LOOKUPVALUE('Dates'[Week Number],'Dates'[Date],'Timesheet Reports'[Date])

And I have multiple rows per person for each week, with different hour values, including some negative values. Not sure if that matters.

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:

Total Hours for Week Selected = CALCULATE(SUM('Timesheet Reports'[Hours]),ALLSELECTED('Timesheet Reports'[Financial Week]))

and this gives me the expected number of total hours, which is 3283.6 for week 17.

Then, if I create a column with the formula:

Hours Percentage = 'Timesheet Reports'[Hours]/3283.6

It gives me the expected values.

However if I use the following formula for that column:

Hours Percentage = 'Timesheet Reports'[Hours]/[Total Hours for Week Selected]

it doesn't work. Instead it just gives me the value 1 for every row.

I checked it when Hours Percentage = [Total Hours for Week Selected] and it gives me the hours value for each row. So that's where the problem is.

Then I tried

Hours Percentage = 'Timesheet Reports'[Hours]/CALCULATE(SUM('Timesheet Reports'[Hours]),ALLSELECTED('Timesheet Reports'[Financial Week]))

but clearly it is the same problem because it's just the same formulas.

I'm still a beginner with Power BI formulas so don't have enough knowledge to do this.

Please can someone help me, and explain step by step how the formula works if possible so I can understand and replicate it for myself next time!

Thanks 🙂

[v-lili6-msft]

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

[Erf19]

sorry, this doesn't seem to work either

[v-lili6-msft]

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

[amitchandak]

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

[Erf19]

Hi, thanks for your response.

I just tried this. First got an error about number of arguments in 'allexcept' so I changed it to:

CALCULATE(SUM('Timesheet Reports'[Hours]),allexcept('Timesheet Reports','Timesheet Reports'[Financial Week]))

But this doesn't give me the results I want. Instead I don't know where the number is coming from that it gives me.