Individual Tank volume as a % of total Tank volume

[BudMan512]

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

[speedramps · Accepted solution]

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

[speedramps · Accepted solution]

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

[BudMan512]

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

[speedramps]

Hi again Budman

Click here to download a solution

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

[BudMan512]

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

[speedramps]

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 !

[BudMan512 · Accepted solution]

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