Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Erf19
Frequent Visitor

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

Hi all,

 

I have a table called Timesheet Reports, like this:

NameDateWeekHours
Clare10/02/2020130
Clare17/02/2020220
Clare24/02/2020340
James10/02/2020119
James17/02/2020230
James24/02/2020325
John10/02/2020135
John17/02/2020234
John24/02/2020322
Kate10/02/2020112
Kate17/02/2020233
Kate24/02/202031.5
Lily10/02/202014.5
Lily17/02/2020210
Lily24/02/202039
Sally10/02/2020120.5
Sally17/02/2020214
Sally24/02/2020319

 

 

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.:

helppp.PNG

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 🙂
5 REPLIES 5
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

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:

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.