cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

New Member

## Total by group, skip the duplicate row

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

1 ACCEPTED SOLUTION
Community Support

Hi @lwibis ,

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.

2 REPLIES 2
Community Support

Hi @lwibis ,

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.

Super User

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?