Relative newbie here to Power Pivot.
I've already scoured a few related posts of similar problems but I've struggled to see the answer to my specific issue.
I have a table relating to delivery drivers. Each row is a different "leg" of a driver's day. EG if a driver picks up a loaded van and goes out to do deliveries they will have 1 row for one particular date. If they come back and complete deliveries from a second van they appear twice on a given date. We pay subcon drivers a flat fee for 10 hours and sometimes they appear once, or twice. I need to tag on a calculated field to show their fee correctly (I'm using PP because it's faster than processing in excel and all drawn from a system driven data source). I essentially either want the full fee to appear on the first row, or alternatively show the total fee split evenly into each leg of the journey.
One thought was to try and find a formula to self identify if a row is the first one on a certain day for that driver in the table and apply the full fee there (couldn't crack it).
Another was to do a countifs on that driver for the day and divide the total fee by the returned result.
Below is an example shown in another thread, but it returns the total times the driver shows up. How would I filter based on the driver's appearances on just the specific day on the row? IN excel I'd just run an expression to say filter the date on the date shown on the current row...but I can't find how to do this online!
=calculate(COUNTROWS(Query),ALLEXCEPT(Query,Query[DriverDriverDriver]))
Any help understanding how the allexcept funtion is working above would be appreciated and also if you know of a way to apply the number to just the first row that'd be amazing too!
I've already scoured a few related posts of similar problems but I've struggled to see the answer to my specific issue.
I have a table relating to delivery drivers. Each row is a different "leg" of a driver's day. EG if a driver picks up a loaded van and goes out to do deliveries they will have 1 row for one particular date. If they come back and complete deliveries from a second van they appear twice on a given date. We pay subcon drivers a flat fee for 10 hours and sometimes they appear once, or twice. I need to tag on a calculated field to show their fee correctly (I'm using PP because it's faster than processing in excel and all drawn from a system driven data source). I essentially either want the full fee to appear on the first row, or alternatively show the total fee split evenly into each leg of the journey.
One thought was to try and find a formula to self identify if a row is the first one on a certain day for that driver in the table and apply the full fee there (couldn't crack it).
Another was to do a countifs on that driver for the day and divide the total fee by the returned result.
Below is an example shown in another thread, but it returns the total times the driver shows up. How would I filter based on the driver's appearances on just the specific day on the row? IN excel I'd just run an expression to say filter the date on the date shown on the current row...but I can't find how to do this online!
=calculate(COUNTROWS(Query),ALLEXCEPT(Query,Query[DriverDriverDriver]))
Any help understanding how the allexcept funtion is working above would be appreciated and also if you know of a way to apply the number to just the first row that'd be amazing too!
