Difficult Measures

[marsclone]

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:

Test File

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

[Anonymous]

Hi @marsclone ,

Could you post a scrubbed sample of the raw data and how it is formatted?

[marsclone]

Hi @Anonymous

A test file is in my post.
The original data which is in excel, is really big and contains sensitive data.
This data is also a little bit more complex to explain how everything works.

[Anonymous]

You need some sort of indicator that tells what source the product comes from. What indicates that it came from a third party?

[marsclone]

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?

[Cmcmahan]

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"
             )
         )

[marsclone]

@Cmcmahan 

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

[Cmcmahan]

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:

•   You could add rows to your data where [Location]=3rd Party and [Destination]=Warehouse so that you can group it properly. This is a bad option, since it requires a lot of manual editing.
• You could create a calculated table that calculates each year of A, B, & 3rd party sourcing.
• The best option would be to add a Source column to your future data. That would solve this issue immediately and cleanly.
• Another bad option is to play with table formatting to end up with something like this:
  
  I made this monstrosity by swapping the Rows and columns of your matrix, and then creating another measure that just all products and is filtered by company like your original matrix. After that, I created 2 tables of data seperated by year, turning off word wrap, shrinking the year columns to nothing, and positioning the 3rd party table and total table to line up with the matrix.  It technically displays all the info you want, but will fail if there's a time period with no 3rd party products, since the years won't line up.  Trying to maintain this setup will be cumbersome at best.  I seriously do NOT recommend doing this.

[Cmcmahan]

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.

[marsclone]

@Cmcmahan

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

[Cmcmahan · Accepted 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

[marsclone]

@Cmcmahan 

I want to thank you again, with your help i've reached the wanted result!

Regards Marcel