SUM value for identical reference

[clem312]

Hello,

I would like to SUM the field "MaximumTargetCount" for each "@etude_pays_quota"

For example, in the image below the first and fourth line are the same @etude_pays_quota=1205FR0663502. So I would like to SUM 10 and 11 the values for this 2 lines :

[BA_Pete · Accepted solution]

Hi @clem312 ,

In Power Query, you would select your [@etude_pays_quota] column, go to the Home tab > Group By, then in the dialog choose the SUM operator on the [MaximumTargetCount] column as your aggregated column. Note that this will fundamentally change your table structure, and will lose any columns that aren't included in the group operation.

If you want to do this in PQ, but also need to retain your table structure, you would perform the same process as above, but you would ALSO include a column in the Group By dialog with the Operator 'All Rows'. You could then expand this 'All Rows' column back out to reinstate the original columns whilst keeping your group sum column. Note that this will create duplicate SUM values where there are more than one [@etude_pays_quota] rows that have been summed in the group operation.

In DAX, you would just create a simple measure like this and add it to a visual along with [@etude_pays_quota] and the sum will be grouped automatically:

_targetSum = SUM(yourTable[MaximumTargetCount])

Pete