Getting a correct sum when having multiple rows and multiple tables

[InspectahJ]

Hello,

I could not find the answer to my inquiry- if it already exists, I would very much appreciate a link.  I am pretty new to this so would appreciate any help I can get.

I have one table of order data, in that table there can be several rows with the same order number (on each row I have COGS, sales etc). 

I want to combine that table with another table where I have logistics cost per order number (those are the only two columns). That table only has one cost per order number. I would like to somehow get a total cost (with COGS + logistics cost) per order number. 

The two tables have a relationship and I get the total cost (COGS+logistics cost) to work on a total level. Whenever I try to break it down to for instance customer or ordernumber it does not work. 

Would appreciate any help, have googled like crazy but cant find the right solution.

James

[v-juanli-msft]

Hi @InspectahJ

For the picture from your last reply, Are "COGS1", "COGS2", "COGS3" columns from table1 and is "summa logcosts" a column from table2?

I don't see  "summa logcosts" from table2, why?

The two tables have a relationship and I get the total cost (COGS+logistics cost) to work on a total level.

From all your replies, i still don't find any total cost (COGS+logistics cost), does total cost (COGS+logistics cost) mean to add column "COGS" from table1 and column "logistics cost" from table2?

For example

table1                                                         table2          

COGS  ordernumber                                  logistics cost            ordernumber

1          1                                                     1                               1

2          1                                                     2                               2

3          1

3          2

4          2

5          2

so total (COGS + logistics cost) per order number shoule be:

total                        ordernumber

(1+2+3)+1              1

(3+4+5)+2              2

Is my understanding right?

Best Regards

Maggie

[InspectahJ]

Hi Maggie,

I appreciate your help! I got it solved with a matrix table earlier today.

Thank you for trying to help me!

James

[v-juanli-msft]

Hi @InspectahJ

Could you show us how you slove it with a matrix table at your convenience?

Such as the screenshot and measures or calculated columns created to help sove the problem.

This will help others who have the similar problem as yours.

Best Regards

Maggie

[Anonymous]

@InspectahJ

I suggest you drop some sample of your tables. It is not clear to me. For example, if the tables have relationships, then why do you want to combine them? Based on which filed they have a relationship? Which kind of a relationship they have? one-to-many? Is there a fact table or lookup table in between? 

[InspectahJ]

@Anonymous

Thank you for you time!

I created a relationship as I thought that would be enough to get the combined cost to work. There is a one to many relationship right now. Not sure what kind of samples I should provide?

I will try to make it clearer:

I have two tables. In the first one I have all the order rows from our ERP. On each row there is a ton of data (such as customer, what product, amounts and other). The order number is not unique in this table as an ordernumber can have several rows.

The second table has only two columns: ordernumber and cost per ordernumber. In the column ordernumber there are unique values. 

I am trying to find a way in which I could get a total cost per order number with the data from these two tables. If possible I would like to be able to perform analysis on the total cost per customer or article  etc. 

I have only been doing this for about a month and have an accounting background, so I am sorry if this is a bit blurry from my side...

[Anonymous]

@InspectahJ

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[InspectahJ]

Ok - here is try:

The relationship: Two tables are called "transaktioner" and "StorexCostsTotal"Relationship between the two tablesTable1: transaktioner. Showing the COGS value - which I would like to add with the value LogCosts from Table 2: StorexCostsTotal (see next screen shot)Table1: transaktioner. part 1Table2: StorexCostsTotal: ordernumber is unique

[InspectahJ]

So on a total cost level it works: see below:The total for everything is correctIf I try to divide the data into any categories - it becomes wrong (same amount in every category)