Power BI DAX Calculation

[RoadeoAnalytics]

Hi,

I would like some help figuring out how to derive at revenue per driver for the attached dataset. I've gotten this to work on a high level, but when it's filtered by driver, it returns blank. Please see details below.

Since I can't attach the dataset, please see link below to download.

https://drive.google.com/file/d/1pZyTw0DSQNOq7hbtpYryVJkCFQlZio4g/view?usp=share_link

1) On the Pickup and Delivery tab lets take Pickup: Packages visual as an example. The revenue is $516.88 and total packages picked up is 3,382. I have the below formula to come up with revenue per package, which is about $.15.

Rate$/PU #PKG = DIVIDE('All P&D Tables'[PU PKG $AMT],'All P&D Tables'[PU #PKG])

2) Then when we flip over to the Driver Perfomrnace tab, I would like to take this rate and multiply it by pakcages picked up (PU #PKG AI) to derive the revenue from picking up these packages. So I have the formula below to do this and it does work when I filter by company or by Settlment Date, however it does not work when filtered by individual driver. It returns blank. Please help.

RevenueDriverPU = SUM('All P&D Tables'[PU #PKG AI]) * AVERAGE('All P&D Tables'[Rate$/PU #PKG])

[RaocoSolutions · Accepted solution]

RevenueDriverPU =
VAR _avgPU = Calculate(AVERAGE('All P&D Tables'[Rate$/PU #PKG]),ALL('All P&D Tables'[DRIVER NAME]))
return
SUM('All P&D Tables'[PU #PKG AI]) * _avgPU

[RoadeoAnalytics]

@RaocoSolutions From initial testing, works great! Thank you, @RaocoSolutions, really appreciate your help with this. 

[AndrewPF]

I think you need to revisit the merging of your tables. 

If the Rate$ is 0, whatever else you do, the result will be 0. 

[RoadeoAnalytics]

Is there no way to use the rate from one table and multiply it by values in another table?

So if the rate comes from PKG Summary table, let's say it's $0.43. Can we not populate this value in Additional Information table? Maybe add a column that populates these rates? The calculations would need to be done on a Settlement Date level. Meaning that for each settlement date it would need to run the calculation. Please let me know if you might have any other ideas to solve this. Appreciate your time and help.

[AndrewPF]

Your Rate$/PU #PKG is zero for every occurence of that driver which is why the SUM * AVERAGE is returning nothing. 

If you want to show zero instead of blank, change your measure to: 

RevenueDriverPU = SUM('All P&D Tables'[PU #PKG AI]) * AVERAGE('All P&D Tables'[Rate$/PU #PKG]) + 1 - 1

[RoadeoAnalytics]

Yes, the Rate$/PU #PKG is zero for every occurance of the driver because these are two different tables where I merged the queries into one table called All P&D Tables.

The rate comes from the PKG Summary table where I devide PU PKG $AMT by PU #PKG to derive at compensation per package.

I then want to take this rate and multiply it by the PU #PKG AI field in the Additional Information table to get the revenue for package pickup for each driver. Is there any way to do this?

[AndrewPF]

When I open the downloaded version, I can't see the visual you've highlighted, nor can I see any measure or column called RevenueDriverPU.  When I select the driver Amadi, I see this: 

[RoadeoAnalytics]

Please see link to updated file:

https://drive.google.com/file/d/1pZyTw0DSQNOq7hbtpYryVJkCFQlZio4g/view?usp=sharing

[AndrewPF]

I've had a quick look at your file and, for me, the PU #PKG / PU #STOPS chart in the Driver Performance tile does update when I select a driver.  It goes blank if there is no data for a particular driver (e.g. Alf) but it's populated for one who does have data (e.g. Amadi). 

[RoadeoAnalytics]

Hi Andrew, thankyou for taking a look. Could you please let me know how you did that? If you would be able to send me the file back and let me know the changes made, that would be greatly appreciated.

I cannot replicate on my side. Just tried for Amadi and it turns up blank where there are 4 packages that should be paid for. Please see screenshot below.

[AndrewPF]

You could try wrapping a CALCULATE around your SUM and AVERAGE. 

Also, check that the drivers by whom you are filtering have populated values for all the variables within your formulae, otherwise you will end up with blanks. 

[RoadeoAnalytics]

Thank you, Andrew, for your reply. I tried wrapping CALCULATE but it made no difference.

I have merged queries into one table. And yes, the Drivers would be a field that's from a different table and was merged into one big table called All P&D Tables. I think the formula needs to point to these rows where the data is. I've included a link to the pbix file in my original post. Any chance you can take a look?