issue with Sum(Value)

[Raju_6952]

Hi,

I have value field coming from excel file and sum(value) is giving wrong value. any idea why the sum(value) is wrong here. I am using measure like sum(value) not used any set analysis.

Thanks in advance,

[P_Kale]

Hi,

What values do you have in excel. Pl share the file

[Or]

Presumably, what you're showing as "one row" is actually 431 rows of data with the same unique dimension combination. You should be able to confirm this by adding e.g. Count(Value) as a measure.

[Raju_6952]

Hi @Or ,

Yes count(Value) is 431 only, any idea to make it work .

[Or]

Fix whatever is causing the duplication in the data model, or don't use sum(), or use aggr() in some way to get around whatever is wrong with the data model. Impossible to say without knowing what the data model is like.

[rwunderlich]

Since all 431 rows have the same value for "Value", I assume you could use Sum(Distinct Value). 

As @Or suggested, you may want to investigate the duplication in the data model to determine if it's valid.

-Rob

[Raju_6952]

Hi @rwunderlich ,

Thanks for the update, I used distinct to remove the duplicates.

Regards,

Raju

[Or]

Sum(Distinct) is almost never a good idea. For example, consider that in the below dataset, the sum of A will be 3, the sum of B will be 5, and the total sum distinct will be 6 rather than 8.

Dim1, Value

A, 1

A, 2

B, 2

B, 3

I'd strongly advise either fixing the data model or, if that's not feasible for some reason, working with aggr(). Sum(Distinct) is (almost) never the answer.

[Raju_6952]

the problem is that on the source side data is loaded twice or thrice with similar data as follows.

need to consider only value for latest modified date for each primary key field.

[rwunderlich]

Then I would suggest something like:

Sum(Aggr(FirstSortedValue(Value, -ModifedDate), PrimaryKey))

Not sure if you will need "NoDistinct" in the Aggr(). Didn't get a chance to test it. 

-Rob