Forum Discussion
Comparing values from 2 tables, do not sum values
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
5 Replies
Hi Anonymous
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.
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
Hi,
Very difficult to suggest a solution without having access to your file.
