Values to high does to data structure

[Justas4478]

Hi, I have holiday report and for majority is working fine.

There is problem with 'Leave Brought Forward', 'Leave Booked' and 'Leave Balance' data when I get it in to power bi.

As you can see in the sample picture below shows that employee 80419990 has 'Leave Brought Forward' 45 hours but it is recorded 19 time when it is actualy only once.

Since this data seams to be recorded for each row per person it massively inflated numbers when I use it in power bi, same goes for 'Leave Booked' and 'Leave Balance' columns.

 

I can not change the excel file itself since I am not the once extracting it and it gets replaced every week any way so trying to clean it every week would be waste of time.

So I need to find solution in power bi desktop.

These are the numbers I get when I add them to matrix table.

 

'Entitlment' data is correct since they are only recorded once as you can see in first sample picture.

If there is any questions please let me know.

If it helps here is data samle file: https://we.tl/t-9Lsfnyn0c7 
Thanks

[lbendlin]

Seems to be a very small problem, only affecting two employees,

I would solve that with a MAX measure.

[Justas4478]

@lbendlin That is only if we look at 'Leave Brought Forward' column.
'Leave Booked' and 'Leave Balance' still has that problem and across all employees.

I dont use 'Leave Brought Forward' in my matrix tables.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Justas4478 · Accepted solution]

@lbendlin I manage to work out solution.
I dublicated main table and deleted 'Leave Brought Forward', 'Leave Booked' and 'Leave Balance' columns from the original table.
Then on the dublicated I removed all the columns except 'Staff Number','Full Name','Start Date' and 'Leave Brought Forward', 'Leave Booked' and 'Leave Balance'.

After that i grouped dublicate table like this.

After that I merged both tables.

And expanded grouped table

That seams to have solved my problem.
There might be different way to do this but its seams to be working without any problems.