Forum Discussion

RatanaPOUY's avatar
RatanaPOUY
Frequent Visitor
6 years ago
Solved

Sum distinct base on column

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

Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    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.

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    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