cancel
Showing results for
Did you mean:
Helper III

## Individual Tank volume as a % of total Tank volume

I am creating a report using Customer, Tank Volume and Gallons

I want to know how many gallons are sold to each tank.

I am summing the Gallons by Customer.

However, sometimes a customer has more than one tank and they can be different sizes, and Gallons are tracked by Customer not by individual Tanks.
I want to calculate  gallons for each tank based upon the weighted average of its size as a percent of total Customer Tank volume.

To do this I need to list the size of individual tanks each customer has and I need to also list the total volume of all tanks the customer has.
I have been able to either list the size of each tank the customer has or I can list the total volume of all tanks for the customer but I can't seem to get both on the same report.
Is there a way?

I am new to Power BI so I may not be thinking of this in the right way. I'd appreciate any help I can get.
Thanks

2 ACCEPTED SOLUTIONS
Super User

Hi Budman

Can you share an example of the raw data

For example does it look like this with a customer and sales table, and you simply want to weight the % of sales based on the customer's total tanks?

Note I have chosen some data that exceeds or does not reach the capacity.

 CUSTOMER TABLE Customer ID Tank ID Tank size in gallons A 1 10 A 2 20 B 3 15 C 4 20 C 5 20 C 6 50 SALES TABLES Customer Gallons purchased A 80 B 60 C 45
Helper III

Thanks Speedramps! you went out of your way to create this report for me and I do appreciate it.  I don't fully understand every aspect yet, I do get the big picture and I can sort out the DAX.  If I could give you 5 stars, I would.  Regards

6 REPLIES 6
Super User

Hi Budman

Can you share an example of the raw data

For example does it look like this with a customer and sales table, and you simply want to weight the % of sales based on the customer's total tanks?

Note I have chosen some data that exceeds or does not reach the capacity.

 CUSTOMER TABLE Customer ID Tank ID Tank size in gallons A 1 10 A 2 20 B 3 15 C 4 20 C 5 20 C 6 50 SALES TABLES Customer Gallons purchased A 80 B 60 C 45
Helper III

Yes this is what I am trying to do.  So next to the "Tank size in gallons" I need to have a column "Total customer tank volume." For Cutomer A it would be 30 gallons on each of the two rows, Customer B would have one row of 15 gallons and Customer C would have three rows of 90 gallons on each.  Then I can calculate each tank's percentage of the customer total tank volume, multiply by the customer gallons and get the gallons attributed to each tank.

Thanks for the extra effort of creating the tables.  It makes it much easier to talk about.

Thank you

Super User

Hi again Budman

Look at the relationships, the dax measures and the report carefully.
Note I have added dates to make it a bit more complicated, because real life is complicated !

I think “Gallons for this tank“  is the answer you are looking for.

Please hit solve and leave kudos if it work. Thanks

Helper III

Thank you speedramps.  This looks very interesting.  Unfortunately I have to wait for our IT department to update my version of Power BI Desktop before I  can take this out for a spin.

I am very much looking forward to it and will be happy to provide Kudos and Accept as Solution once I do. Thanks for your time and knowledge.  - Bud

Super User

Hi again Budma

Gosh you must have a very old version, because I have no used any new features.

It is annoyimg when the IT Department dont refresh Power BI Desktop with each monthly update. It hinders developers sharing PBIX files from different versions.

Instead of using Power BI Desk Top, you can login to the cloud Power BI service.

Then click on > My Workspace > New > Upload a file > etc etc

Good luck !

Helper III

Thanks Speedramps! you went out of your way to create this report for me and I do appreciate it.  I don't fully understand every aspect yet, I do get the big picture and I can sort out the DAX.  If I could give you 5 stars, I would.  Regards

Announcements

#### Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

#### European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

#### Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors