- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Power BI DAX Calculation
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@RaocoSolutions From initial testing, works great! Thank you, @RaocoSolutions, really appreciate your help with this.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Please see link to updated file:
https://drive.google.com/file/d/1pZyTw0DSQNOq7hbtpYryVJkCFQlZio4g/view?usp=sharing
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
05-16-2024 08:36 AM | |||
Anonymous
| 06-25-2024 01:32 AM | ||
Anonymous
| 02-27-2023 07:16 AM | ||
07-14-2024 10:24 PM | |||
04-27-2024 03:07 AM |
User | Count |
---|---|
141 | |
111 | |
81 | |
61 | |
46 |