Show weekly submitted hours as a %

[PPStar]

Hello. 

I have a table called WeeklyTimesheet

Week 1     | Weekly.Hours(submitted) 

1               | 123

2               | 456

3               | 789

..

45             | 1011

I need to show weekly submittted hours as a %. 

So 

Week 1 = 123 / Sum of all the submitted hours

Week 2 = 456 / Sum of all the submitted hours

Week 3 = 789 / Sum of all the submitted hours

Week 45 = 1011 / Sum of all the submited hours

Can someone please help me do this. 

I wrote the following formulae, but it just returns 1 every time

Measure = calculate(DIVIDE(SUM('WeeklyTimesheet'[Weekly.Hours (submitted)]),SUMX('WeeklyTimesheet_Project&Ops','WeeklyTimesheet'[Weekly.Hours (submitted)])),ALLEXCEPT('WeeklyTimesheet','WeeklyTimesheet'[Week of Year]))

Thanks. 

[PPStar]

Hello. There has been a slight change in requirements. I have managed to upload a dummy PBIX file (Which has the instructions on here)

Basically, we have 2 tables. 

Weekly Timesheet and Admin Timesheet

The requirement is to Display a visual which shows the weeklyTimesheet submitted hours by Week No as a Percentage.

Submitted Hours for Week 1 on Weekly Timesheet / Week 1 on Weekly Timesheet + Week 1 on Admin_timesheet -- plot this value on week 1 on visual

Submitted Hours for Week 2 on Weekly Timesheet / Week 2 on Weekly Timesheet + Week 2 on Admin timesheet -- plot this value on week 2 on visual

I have with an example of how the calculation should work. weeklytimesheet.pbix

[gmsamborn]

Hi @PPStar 

I took a look at your model and noticed would make sense to create a date table and relate both timesheet tables to it.  That way any visuals can be based on a week column in the date table.

weeklytimesheet.pbix

[Dangar332]

Hi, @PPStar 
@PPStar 
check below code and image

just adjust your table and column anme

Measure 4 = 
var a= SUM('Table (3)'[hours (submitted)])
var b = CALCULATE(SUM('Table (3)'[hours (submitted)]),ALL('Table (3)'))
return
DIVIDE(a,b)

conver measure to percentage from format pane
or  update divide(a,b)*100

[Dangar332]

Hi, @PPStar 

i am taking only first ten(10) row of data you provide in image  to show you.

so don't confuse with your output

if not work then let me know

[Dangar332]

Hi, @PPStar 

try below

result=
var a = SUM('WeeklyTimesheet'[Weekly.Hours (submitted)])
vra b = calculate(SUM('WeeklyTimesheet'[Weekly.Hours (submitted)]),
          removefilters('WeeklyTimesheet','WeeklyTimesheet'[Week of Year])
          )    
return
divide(a,b)

[PPStar]

var b is incorrect, its returning the sum of all the hours. It should just return the value of hour submitted for that week. 

[Dangar332]

Hi, @PPStar 

as you say in 

Week 1 - should be 6156.20  / 322,257.37

Week 2 - should be 7440.01 / 322,257.37 and so fourth

it calculate as above

sum of that week  divided by sum of all week

[Anonymous]

SELECTEDVALUE effectively returns the value of a column when it has exactly 1 row, so in tables you can use it to return the value of any unique identifier. Swap the SELECTEDVALUE statement to SUM and you should be just fine

[PPStar]

Sum just returns the Value 1 for everythng

Measure = var total_hours =

                            CALCULATE(

                                      SUM('WeeklyTimesheet'[Hours (submitted)]),

                                      ALL('WeeklyTimesheet'[Week of Year]))

RETURN

DIVIDE(

       SUM ('WeeklyTimesheet'[Hours (submitted)]),

       total_hours,

       0

       )

Week 1 - should be 6156.20  / 322,257.37

Week 2 - should be 7440.01 / 322,257.37 and so fourth

[Anonymous]

Weekly Submitted Hours % Total = 
VAR _total_hours = CALCULATE(SUM('WeeklyTimesheet'[Weekly.Hours(submitted)]), ALL('WeeklyTimesheet'[Week of Year]))
RETURN
DIVIDE(SELECTEDVALUE('WeeklyTimesheet'[Weekly.Hours(submitted)]), _total_hours, 0)

[PPStar]

Thanks for your reply, but this just gives me a blank value in my table : 

Measure = var total_hours =

                            CALCULATE(

                                      SUM('WeeklyTimesheet'[Hours (submitted)]),

                                      ALL('WeeklyTimesheet'[Week of Year])

                                    )

RETURN

DIVIDE(

       SELECTEDVALUE('WeeklyTimesheet'[Hours (submitted)]),

       total_hours,

       0

       )

Below is what i see

[Anonymous]

If you could put the pbix in drop box or something similar and share the link here so I can see the file that would be helpful. You could try changing the total hours variable to :
CALCULATE(SUM('WeeklyTimesheet'[Weekly.Hours(submitted)]), ALL('WeeklyTimesheet'))

[PPStar]

i have a demo pbix file, but i am blocked from uploading anything from corporate devices.  Is there another way?

[PPStar]

The Total hours variable is actaulyy fine.  Anyway, i changed it to below

Measure = var total_hours =

                            CALCULATE(

                                      SUM('WeeklyTimesheet'[Hours (submitted)]),

                                      ALL('WeeklyTimesheet')

                                    )

RETURN

total_hours

And it returns the correct number 

I thikn the issue is the SELECTEDVALUE. 

Im not expert, but how does DAX know the selected Value, can we change that to something else?

[PPStar]

Hi, 

That didnt work either. 

My file i have has lots of confidential information, so i cant share. 

The data is all coming from a data lake. 

Ill see if i can mimic the data using Enter Data to create a bogus table. 

Thanks 

[Anonymous]

Are there multiple rows for each week number in your table? I write the measure under the assumption it was 1 row per week number, which is why I used the SELECTEDVALUE function. If your data has multiple rows for each week number, try replacing SELECTEDVALUE with SUM

[PPStar]

The data is coming in with lots of columns

Period           | Activity | lots of other columns | Hours Submitted 

01/01/2023  |

01/01/2023  |

The period has dates for every day. 

I just went into Transform Data , selected the Transform Tab, On the Date Icon, i selected Date and inserted Week of Year. 

So in addition to all the default columsn i got, i now have week of year

The only visual i need is the week of year on the x axis and the % of hours submitted per week on the Y axis (which is what i need help with)