Difference Between SUM a column by aggregate function and SUM by a DAX measure

[PaulTran]

I have 4 Table, Sales, Product, Store & Ranking with the relationship as below:

- Product 1:* Sales

- Product 1:* Ranking

- Store 1:* Sales

- Store 1:* Store

When I use the aggregate function, the values is like this and it works perfectly
Product A | Store 1 | Sales 3000 | Rank A

Product A | Store 2 | Sales 4000 | Rank B

Product A | Store 3 | Sales 5000 | Rank C

However, when I use DAX to create a measure, it's simple like SUM(Sales), the result is duplicate like this

Product A | Store 1 | Sales 3000 | Rank A

Product A | Store 2 | Sales 3000 | Rank B

Product A | Store 3 | Sales 3000 | Rank C

Therefore, I think there is a difference in backend between SUM by Aggregate function and SUM by a DAX Measure.

I need to create a measure because there are many conditions to create, how can I make the result of DAX Measure the same with SU

[amitchandak · Accepted solution]

@PaulTran , Both Rank and Sales should be Measure. You can not have ungrouped data from rank

Sum(Sales[Sales])

Max(Rank(Rank])