Fixing available hrs and leave hrs in Tktable

[ambepat]

Hi

@Melissa It’s me again and I have a couple of adjustments I need to make in the TKtable to make correct calculations.

If you remember the last time the scenario was to add the available hours and leave hours to the Tktable.

Now the problem or issue with doing this has resulted in inflating the total available hours and leave hours in a single month. So here is a screenshot of the results and what I am trying to achieve here

Example with Employee 261 if you look at 12/07 Reg hours are 8 but available hours we merged into tktable is 176 but should be 8 and leave hrs we merge is 24 instead should be 8 and the reason for that is in tktable we have multiple dates for 12/07 for the regular hours and I need to find a way to allocate the 8 available hours within those dates so that when we aggregate them we have the correct amount of available hours.
I don’t know if I need to change the look of the data model to fix this.

I have attached the sample file below.
ScenarioDNA.xlsx (5.7 MB)

Appreciate your help in advance

P

[Melissa]

Hi @ambepat,

I’m wondering where this is headed… because your model should be designed for the analysis you need to perform and if the goal is a ‘simple’ aggregation there are easier ways to achieve that, consider this:

Prep Available Hrs

Prep Leave Hrs

Append to prepped TkTable

Simple data model

Simple measures in a Matrix visual

Here’s your updated PBIX
ScenarioDNA.pbix (344.0 KB)

I look forward to your reply

[ambepat]

@Melissa thanks for this. I think this is probably what I needed. I just couldn’t think at this angle but something simple like this. Thanks for the suggestion and solution. Appreciate it.