Forum Discussion

Icon for Post Prodigy rank

Post Prodigy

5 years ago

Solved

Calculate sum (Unique) one table to another table

I have a two tables are table1 and table2.

In Table 1 contain item, country and value and Table 2 contain country code only.

Table1 and 2 the country code is common/related.

I am trying to calculate the sum of value by country code based on the unique item from table1 into table2.

I am looking for new calculated column.

Example:

If item and country code are same then in this case considered the first line item value. (Example- Item123)

Basically I am trying to ignore duplicate line item values.

Data:

TABLE 1		TABLE2
Item	Value	Country		Country	Desired Result (Sum of value)
123	0.038	Ind		IND	0.076
123	0.038	Ind		Uk	1.591
123	0.038	Ind		Us	0.017
123	0.038	Ind		Aus	0.017
124	0.033	Uk		Ue
124	0.033	Uk
124	0.033	Uk
125	0.017	Aus
125	0.017	Aus
126	0.073	Us
127	0.068	Ue
789	0.038	Ind
56T		Ind
897		Aus
1546R	1.558	Uk

Ashish_Mathur
5 years ago
Hi,

Try these measures

Min value = MIN(Data[Value])
Measure = SUMX(VALUES(Data[Item]),[Min value])

Hope this helps.

5 Replies

Ashish_Mathur
Super User
5 years ago
Hi,

For IND, why should the answer be 0.076?
- Saxon10
  Post Prodigy
  5 years ago
  Thanks for the reply.
  
  I am considering only unique items only, Example item 123 and 789 which is sum of value is 0.076.
  - Ashish_Mathur
    Super User
    5 years ago
    Hi,
    
    Try these measures
    
    Min value = MIN(Data[Value])
    Measure = SUMX(VALUES(Data[Item]),[Min value])
    
    Hope this helps.