cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BudMan512
Helper III
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
speedramps
Super User
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 IDTank IDTank size in gallons
A110
A220
B315
C420
C520
C650
   
SALES TABLES  
CustomerGallons purchased 
A80 
B60 
C45 

View solution in original post

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

 

View solution in original post

6 REPLIES 6
speedramps
Super User
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 IDTank IDTank size in gallons
A110
A220
B315
C420
C520
C650
   
SALES TABLES  
CustomerGallons purchased 
A80 
B60 
C45 

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

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
Budman1.JPG

 

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

 

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 !

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

 

Helpful resources

Announcements
Difinity Conference

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

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 â__ December 6-8, 2022

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.