I'm not sure if this is even possible

[Anonymous]

Hi Guys,

I would love some help if possible and i hope i can explain what i need for someone to help.

I have two tables.

Track

Employees

Basically these pull through using SQL to our factory ticket system.

So in employees table there is a entry under Surname with each Track e.g. Export Track 1. In Track as you can see in the picture employees sign into Sutton Track 4 but then they claim entries under that track which you can see in the surname field and quantity claimed field.

What I need to measure is for each specific day if a user has clocked into say Export Track 1 under the "track" column check for that same day what the quantity claimed was. But this then needs to be divided by the amount of clock ins to that track so you can see Sutton Track 4 on 21 sept had 3 people assigned to it.

So far i have a failing code to count a look up filtered which could be going down the wrong path.

measure = COUNTROWS(FILTER(Track,CONTAINS(Employees,Employees[Surname],Track[Track])))

It needs to count quantity column or just grab that column and divide mainly by three.

Any help would be greatly appreciated!

Thanks,

Adam

[mwegener]

Hi @Anonymous ,

does the requirement have to be solved with DAX or can you also use Power Query?

Can you share a screenshot of the model view.

[Anonymous]

Hi,

Anything that resolves it to be honest.

The model data is in a excel sheet in onedrive a bit further up the comments.

Thanks,

Adam