Total of the Pay period but not the grand total

[PowerBIFreak]

Hi Folks,

I tried to search in this community and see if there is any idea to my questions but with no luck. 

So, I'm trying to create a dashboard for the percentage of each categories for each pay period. I have two tables as below. The relationship is "Date" and "Posting Date" to return with the pay period for me.

On the Hours Table, I have created a measure lets say over time percentage. I simply use the "Over Time"/Total Time to get the percentage. And it seems fine to me. However, when I use the filter to filter out "Pay Period #", the percentage is wrong because lets say if I want to see the pay period 1 of the over time percentage, the formula should be the overtime hours for pay period 1/total hours of pay period 1. If that's the case, does it mean I have to create a measure for each pay period? If yes, since I have 4 categories and I have 26 pay periods, that would be 104 measures???😱😭 I'm so confused! Please help!!!

Thank you guys!!!

Somehow I think we need to use variable on this issue, am I right?

***Total Time = Sum(Over time, Sick time, Normal Time, Vacation)

Calendar Table

Date	Pay Period #
December 21, 2020	1
December 22, 2020	1
December 23, 2020	1
January 4, 2021	2
January 5, 2021	2
January 6, 2021	2
January 7 2021	2

Hours Table

Posting Date	Category	Hours
December 22, 2020	Normal Time	20
December 22, 2020	Over Time	15
January 4, 2021	Sick Time	5
January 5, 2021	Over Time	10
January 6, 2021	Vacation	20

[v-easonf-msft · Accepted solution]

Hi, @PowerBIFreak 

You can try frormula as below:

Total = SUM('Hours Table'[Hours])+0

Category Percentage = [Total]/ CALCULATE(SUM('Hours Table'[Hours]),ALLSELECTED('Hours Table'[Category]))

Then create a table visual as below:

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

[PowerBIFreak]

Hello,

Thank you very much, this is exactly what I need. I just have another quick question, can we create a variable in this case?

Thank you.

[Greg_Deckler]

@PowerBIFreak You will need something like ALL('Calendar'[Pay Period #] in your calculation so that you get all of the pay periods in the calculation even though you have filtered them out on the page or visual or whatever.