Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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 |
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
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 |
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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |