Forum Discussion
SUM value for identical reference
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 :
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
1 Reply
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