cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aszpic Regular Visitor
Regular Visitor

Sum values only one time

Hello everyone,

 

I have a table with 2 columns: 'Code' and 'Values'. The Code can be repeated.

The Values column is the same for each code, so 2 Values for 'CodeA' are equal to each other, and 2 values for 'CodeB' are also the same.

What I need is a measure to sum ONE TIME each value for each code, so in the example of the table below I would only need to sum 1 value for CodeA and 1 value for CodeB and the value for CodeC (3+5+7)

 

I thought a way that is using an auxiliar column named 'Unique' which is '1' if it is the first time the Code appears, and '0' if not, and use a CALCULATE function later. But I couldn't find a way to make this 'Unique' columns.

 

The Unique column would be:

Code     Value   Unique

CodeA   3         1

CodeA   3         0

CodeB   5         1

CodeB   5         0

CodeC   7         1

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Sum values only one time

@aszpic

 

Here's the MEASURE that will do this for you...

 

Measure =
DIVIDE (
    SUM ( 'Table'[Value] ),
    CALCULATE ( COUNTA ( 'Table'[Code] ), ALLEXCEPT ( 'Table', 'Table'[Code] ) ),
    0
)

 

Sum Values only 1 per Code.png

 

Good Luck! Smiley Happy

2 REPLIES 2
Super User
Super User

Re: Sum values only one time

@aszpic

 

Here's the MEASURE that will do this for you...

 

Measure =
DIVIDE (
    SUM ( 'Table'[Value] ),
    CALCULATE ( COUNTA ( 'Table'[Code] ), ALLEXCEPT ( 'Table', 'Table'[Code] ) ),
    0
)

 

Sum Values only 1 per Code.png

 

Good Luck! Smiley Happy

aszpic Regular Visitor
Regular Visitor

Re: Sum values only one time

It will take me a while to understand why this works, but it works. Thank you!