Comparing values from 2 tables, do not sum values

[Aydeedglz]

Hi,

I am comparing two different tables and I want to see the difference and value of each category but on table is showing data by row and the other is doing a sum by customer. But the customer is divided by category.

Example:

Customer        Cat 1       Cat 2    Total Table 1    Total Table 2

Customer A     Blue          1        $20                   $1300

Customer A     Black         2        $50                   $1300

Customer A     Orange     3        $60                   $1300

I have areldy choose for Table 2 "Do not summarize". How can I make table 2 bring me totals by category?
And yes I have made a relationship of the categories. 

Note:

For the creating of the report I am using the exact same data in both tables (just for the creation).

Table 1: comming from connected dataset

Table2: commong from excel in sharepoint 

[Selva-Salimi]

Hi @Aydeedglz 

the relation should be based on customer and category. ( you can create a column by concatenating these two column and make relationship) . or you can write a measure as follows:

calculate (sum ( table 2 [total] ), filter (table2 , table 2 [customer] = selecetdevalue (table 1 [customer]) && table 2 [cat] = selectedvalue (table1 [cat] ))

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly. 

[Aydeedglz]

Hi,

I can't do a connectenate in the first table becuase I am connected to a dataset and I am not abled to go to the table where the data is.

When adding the same category of the other table I end up having 4 rows

Category Table 1   Costumer   Total Table 1   Total Table 2   Category Table 2

A                            1                $50                 $50                  B

A                            1                $50                 $100                A

B                            1                $100               $100                B

B                            1                $100               $50                  A

And I only want to see

Category  Costumer  Total Table 1   Total Table 2

A               1               $50                 $50

B               1               $100               $100

[Selva-Salimi]

@Aydeedglz 

I have suggested two solution, have you tried both?!

and also to have a concatenated column you dont need to go to table where the data is generated. just write a column using dax as follows:

column_key = concatenate ([customer] , [cat] )!

[Anonymous]

Hi @Aydeedglz ,

Is there a field in your Table1 and Table2 that is in a many-to-many relationship? From this result, it seems that the many-to-many relationship caused the Cartesian product to appear in the result.

If there is a many-to-many relationship, you may need to optimize your data structure in advance, such as adding a bridge table or merging two tables into one table in advance through DAX or Power Query, because many-to-many relationships can cause many problems.

However, as Ashish_Mathur said, if you don't provide your sample data and the relationship model between your two tables, it is almost impossible for us to find the right solution. Please use sample data to replace the original data in your two tables and provide us with the relationship between the two tables. Thank you.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Very difficult to suggest a solution without having access to your file.