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,
I'm struggling, from which i believe is a difficult measure?
We have four companies, two of them produces products and two trade companies will sell this products.
These trade companies can also buy products by third parties when needed.
Products are delivered directly at the customer, but it can als happen that products are stored in a warehouse.
From these warehouses products are sold, which were made by our self or buyed at third parties.
Now i have a saleslist from the four companies. I can see what is produced by our self, but i also want to calculate what is bought at third parties.
Is this possible with a measure?
Outcome will look like this:
Source | 2017 | 2018 |
Location A | 390 | 300 |
Location B | 936 | 720 |
Third Parties | 247 | 190 |
Total | 1573 | 1210 |
is it also possible to show the price difference between 2017 and 2018?
Thank you in advance.
Kind regards
Marcel
Solved! Go to Solution.
Once again, I highly recommend against using the first visual solution I was able to create. I've created a 2nd matrix in the report that's a better way of working with the data, though still not as good as starting to record the source or adding in 3rd party orders to the original moving forward.
Here's the file: https://drive.google.com/open?id=16uxhevNcQQxO8gUSBclpupGBTHYDhpQV
You need some sort of indicator that tells what source the product comes from. What indicates that it came from a third party?
That’s a problem, from the products that leaves the warehouse, it is not known where they from, own production or bought from third parties.
But when i calculate the total sales from our trade companies minus the products which are made by our self i have the total buying from third parties.
An ugly measure i believe?
So the only way to check if a product is A, B, or 3rd party is to go to the dataset of products made by A, the dataset of products made by B, and make sure that a specific item is not in either list?
Easiest way sounds like it would be to identify and mark every item as either from A, or B, and then the rest MUST be from 3rd parties.
Source = IF( CONTAINS('AProducts', 'AProducts'[itemID], SELECTEDVALUE('WarehouseProducts'[ItemID])), "A",
IF( CONTAINS('BProducts', 'BProducts'[itemID], SELECTEDVALUE('WarehouseProducts'[ItemID])), "B",
"Third Party"
)
)
The unique item per factory is the location, same products are produced by all factories.
All Sales data from all factories and trade companies are in the same table.
I think the measure you showed is not working for this data table?
The Measure would be like:
Sum all sales (quantity) trade companies -/- Sum sales Factory A -/- Sum sales Factory B = Source Third Parties
Ok, so I was looking through your data, and I think I figured out how you have it set up. First of all, an unrelated tip, you should select your Date table, and mark it as a date table in the Modeling tab.
So correct me if I'm mistaken, but what you want is to first figure out how much stuff has Location = Warehouse, and then subtract quantities from Production A and Production B that have a destination of Warehouse.
3rd Party = CALCULATE(SUM('Data'[Quantity]), 'Data'[Location]="Warehouse") - CALCULATE(SUM('Data'[Quantity]), 'Data'[Delivery Adress] = "Warehouse")
That was the easy part. Trying to get it formatted in a table like you wanted got much harder. Since it isn't categorized data like Location, you can't use it as a row in a matrix. A few solutions to that issue include:
OK, I continued playing with this, and figured out one way to get closer to the setup you want.
Create a measure for both A & B, like so:
QtyInWarehouseFromA = CALCULATE(SUM('Data'[Quantity]), Data[Company]="Production A" )
Then create a Total measure:
Total = [QtyInWarehouseFromA] + [QtyInWarehouseFromB] + [3rd Party]
And add these to a matrix:
If you have a large number of production facilities, this can get tedious, but now that each amount is a measure, it can easily be set up at the same level of a matrix.
That is a big effort, you put in answering my question!
I’m really thankful for this!
What i’m looking for is the table you made by this picture:
Is this possible with the measures you mentioned in your last post?
I will play with the solutions you mentioned, maybe you can share the test file which you worked on?
Thank you again, i’m hopeful we will get the wanted result!
Regards Marcel
Once again, I highly recommend against using the first visual solution I was able to create. I've created a 2nd matrix in the report that's a better way of working with the data, though still not as good as starting to record the source or adding in 3rd party orders to the original moving forward.
Here's the file: https://drive.google.com/open?id=16uxhevNcQQxO8gUSBclpupGBTHYDhpQV
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |