Total by group, skip the duplicate row

[lwibis]

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

[v-binbinyu-msft · Accepted solution]

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.

[FreemanZ]

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?