Distinct SUM of grouped values by several columns

[Anonymous]

Dear all

Thanks for helping me. 

This is the structure and sample of the data.

The point is that each container could be used more than once, but TONS shouldn't be aggregate more than once for each container.

It's not possible to divide SUM of TONS by COUNT of IDs because there is container with Tons = 1 and Tons = 2

The results should be the following:

 

Thanks in advance,

Regards from Spain,

Ana Bisbé

[Datatouille · Accepted solution]

Hi Ana, Interesting use case – you will find 2 solutions here : https://1drv.ms/u/s!Ag8khmd3N7ExgRMLmNkLKGDFmUq1

- DAX : I am using variables and a combination of AddColumns, Values, SumX & Context Transition

- Power Query : I am using a simple Group By in the query editor. This method is pretty simple and works if you can get rid of the « Order ID » column.

Tristan

[Anonymous]

Thanks a lot !! It works properly.

Best regards and Merry Christmas !! Happy new year,

Regards from Spain,

Ana