cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marsclone Member
Member

Difficult Measures

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

 

Source20172018
Location A390300
Location B936720
Third Parties247190
Total15731210

 

is it also possible to show the price difference between 2017 and 2018?

 

Thank you in advance.

 

Kind regards

Marcel

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Difficult Measures

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

View solution in original post

11 REPLIES 11

Re: Difficult Measures

Hi @marsclone ,

 

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

marsclone Member
Member

Re: Difficult Measures

Hi @calebmfoster

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.

Re: Difficult Measures

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

marsclone Member
Member

Re: Difficult Measures

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?

Super User
Super User

Re: Difficult Measures

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 Member
Member

Re: Difficult Measures

@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

 

Super User
Super User

Re: Difficult Measures

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

 

snipa.PNG

 

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:
    snipa.PNG
    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.
Highlighted
Super User
Super User

Re: Difficult Measures

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:
snipa.PNG

 

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 Member
Member

Re: Difficult Measures

@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:

 

CCA4D944-5953-464D-97C2-0BC1DB099F59.jpeg

 

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

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)