Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have 3 tables:
address - contains mass and code
manifestaddress - contains keys to connect address and manifest tables
manifest - contains code and date_mnf, transportation_cost
File is here
Code (from address table) is like items in the package, items may have different mass. I want calculate total mass and then calculate a share of all items in the package.
In details I want to get fixed "sum of mass" to use it as denominator in order to get % or share,
it can be done by filtering specific date (ex. date_mnf=04.03.21) and by summing mass (from address table) by code (from manifest table) and by code (from address table)
So, I can't find way to calculate total mass and write share next to every item
In the end after summing up last column (transportation_cost by code) I should have the same amount as sum of transportation_cost in manifest table in the same period of time
code (from manifest table) | date_mnf (from manifest table) | code (from address table) | mass (from address table) | sum of mass | % | transportation_cost | transportation_cost by code (from address table) = % * transportation_cost |
2308 | 04.03.21 | 68720 | 0.1 | 1.3 | 7,6923 | 8097 | 622.8462 |
2308 | 04.03.21 | 68748 | 0.1 | 1.3 | 7,6923 | 8097 | 622.8462 |
2308 | 04.03.21 | 68754 | 0.1 | 1.3 | 7,6923 | 8097 | 622.8462 |
2308 | 04.03.21 | 66076 | 0.5 | 1.3 | 38,4615 | 8097 | 3114.231 |
2308 | 04.03.21 | 67800 | 0.5 | 1.3 | 38,4615 | 8097 | 3114.231 |
One detail: code (from address table) may refer to different code (from manifest table), so that may influence total mass, example bellow:
code (from manifest table) | code (from address table) | mass (from address table) | mnf_date (from manifest table) | transportation value (from manifest table) |
2287 | 66076 | 0.5 | 44253 | 1000 |
2308 | 66076 | 0.5 | 44259 | 8097 |
Any idea?
Hi, @Anonymous
Please correct me if I wrongly understood your model.
One address can have two or more different manifest codes. -> I think I can understand.
But, at the same time, two addresses can have one manifest code. -> I do not know how to create a relationship.
Or, is there any mistake creating a sample?
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
address in manifestaddress table may have 2 or more manifest codes, but manifest codes manifestaddress table can have only one address
Hi,
Sorry, I think I am missing some point.
66076 can have two or more (2308, 2287) -> I can understand.
but, at the same time,
2308 can have two or more?? The sample shows 2308 has 68754, 68748 and more.
2336 can have two or more?? The sample shows 2336 has 69583, 70618 and more.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
any idea how to solve this problem?
Hi,
Sorry that I still do not understand.
Or, do you have another table that shows the primary key number of the grouping?
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi
I simplified problem explanation could you please take a look it in the new request
https://community.powerbi.com/t5/Desktop/Grand-total-of-mass-by-category/td-p/1791879
imagine as 2308 is package in delivery, and 68754, 68748 are items in that packade.
Somitimes item like 66076 can be devided in several packeges like in 2308, 2287
This is how it works
I updated the file to see the whole data set here
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |