topic Re: Total by group, skip the duplicate row in DAX Commands and Tips

Total by group, skip the duplicate row

lwibis — Wed, 08 Feb 2023 20:46:53 GMT

Hi,

I am new in DAX. trying to get Total Cons by Code. if there is a duplicate Cons (code 10, only sum one of it which 535).

I was using SUMX, SUMMARIZE, MAX. But got wrong result for code 40. it gave me 3 where it is supposed to be 4.

Any help, would be much appracited!

Wanted the result as below.

Result
535	10
4	30
4	40
0	50
4	41

The data as follows

Cons	charge	Code
535	52.86	10
535	-4.55	10
0	12.83	10
0	1.71	10
4	6.56	30
0	12.35	30
0	6.87	40
3	1.56	40
1	1.11	40
0	4.5	50
4	12.4	41
0	6.55	41

Thanks,

-L

Re: Total by group, skip the duplicate row

FreemanZ — Wed, 08 Feb 2023 21:31:30 GMT

hi lwibis

not sure about the logic behind. Like why cons 1 and 3 is not included in the result? How comes code 50 for cons 0?

Re: Total by group, skip the duplicate row

Anonymous — Thu, 09 Feb 2023 01:57:16 GMT

Hi lwibis ,

Please try below steps:

1. below is my test table

Table:

2. create measure with below dax formula

Measure = VAR cur_code = SELECTEDVALUE ( 'Table'[Code] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[Code] = cur_code ) VAR tmp1 = CALCULATETABLE ( VALUES ( 'Table'[Cons] ), tmp ) RETURN SUMX ( tmp1, [Cons] )

3. add table visual with field and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.