Sum distinct base on column

[RatanaPOUY]

hi,

i have a detail table as follow:

Table name: Daily Transaction

then create a new table to distinct delivery number

Delivery Order = DISTINCT('Daily Transaction'[Delivery])

the problem is formula of sum Volume doesn't work.

CALCULATE(SUMX('Daily Transaction','Daily Transaction'[Volume]))

Thanks,

Ratana

[v-xicai]

Hi @RatanaPOUY  ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai · Accepted solution]

Hi @RatanaPOUY ,

You may create measure like DAX below.

Sum Volume= CALCULATE(SUM('Daily Transaction'[Volume]),FILTER(ALLSELECTED('Daily Transaction'), 'Daily Transaction'[Delivery] =MAX('Daily Transaction'[Delivery])))

Or you can just change the show aggregation of Volume to Sum.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I'm not sure what is the purpose of the new table, but if you only require to achieve the result of summing the Volumn column based on Delivery, then you can access  "Edit Query", duplicate the original Table, Go to Transform tab, and then click on the Group By function, this will show a dialog in which you will chose the parameters of groupings and the aggregation you need.

however, if the purpose is only to show the results in a visual, then this can be achieve by simple aggregation on the visual itself.