Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
BudMan512
Helper IV
Helper IV

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors