How we can use both Distinct and sum functions in set analysis?
See the below expression where i need to take the distinct values of the field Number before taking the Sum of Amount
Sorry, I don't understand what you mean with " i need to take the distinct values of the field Number before taking the Sum".
Could you explain this with some lines of sample data and your expected outcome?
See the below example
here i want the result as
here you can see like we are taking the distinct of Test1 and after that i want to sum the field Test3.
My suggestion is to move the calculation into the load script. Maybe you'll also need to calc count of such records, sum of some fields, etc. But you'll definitely avoid extra calculations in run-time.
Did you try Stefan's solution or Maxim's suggestion?
If it still does not work and you this as Set Analysis - post your data+error+desired result.
I simply added TEST1 and TEST 2 as dimensions into straight table and in Expression i used below and I could get what you described.