Sum of values for distinct IDs only

[JRA21_13_19_25]

Hello,

How do I create a measure that sums values for distinct Ids only? 

For example, in the table below I want to the sum values for just the distinct IDs so the answer should be 5+6+8+10 = 29

Thanks.

IDs	Value
1	5
1	5
2	6
3	8
4	10
4	10

[JRA21_13_19_25]

Thank you very much @Anonymous and @speedramps for your help 😁

[Anonymous]

Happy to help. Whichever answer worked for you, please mark as the solution so that other forum members can benefit as well when searching for solutions.

[speedramps]

Sorry my bad, I didnt read the question.

Just change my previous instructions to the following.

Max value = MAX(yourtable[Value])

Sum of distinct values =

SUMX( DISTINCT(yourtable[IDs]), [Max value])

The answer will = 34 !

However, I am a bit disappointed.

I taught you how to do this in my last message ... so you should have been able to change it yourself.

I much prefer to teach people DAX than give a solution.

Please read my explanation and learn how it works to improve your skills.

Please click thumbs up and accept as solution.  😎

[Jos_Woolley]

Hi,

I'd use

MyMeasure =
SUMX( DISTINCT( MyTable ), MyTable[Value] )

though not sure I've understood correctly.

[speedramps]

Hi Jo's

Thanks for trying to help but it wont give the correct answer. Try it and then read and try my solution. Many thanks for trying !!!

[vapid128]

What happen if values are not same in same ID?

IF we want to average:

Sumx(values([IDs], calculate(average([Value]))

IF we want to get the max:

Sumx(values([IDs], calculate(max([Value]))

[speedramps]

Try this ....

Create 2 measures ......

Max value = MAX(yourtable[Value])

Sum of distinct values =

SUMX( DISTINCT(yourtable[Value]), [Max value])

Then drag [Sum of distinct values] into a card visual.  It = 29 !

How it works ....

SUMX is an iterator

DISTINCT(yourtable[Value]) creates a tempory table of the distinct values

[Max value] will get the max value for a "context".

(eg a context might me year, month, a supplier or customer)

Therefore     SUMX( DISTINCT(yourtable[Value]), [Max value])

will sum the max value for each distinct value "context".

Since there can only be one max value per distinct value, the answer will = 29

Understand ? 

I get a lot more pleasure teaching dax than giving solutions.

Thanks for reaching out for help.

I have helped you, now please help me by giving kudos.

Remeber we are unpaid volunteers.

Click the thumbs up and accept as solution button. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

[JRA21_13_19_25]

@speedramps But what happens if different IDs have the same value like ID 1 and ID 4 in the table below - In the case below the answer should be 34 and so your method will not work as it needs to also consider the IDs. 

IDs	Value
1	10
1	10
2	6
3	8
4	10
4	10

[Anonymous]

@JRA21_13_19_25 , this measure should work:

DistinctValueSum =
    SUMX(
        SUMMARIZE(
            'YourTable'
            ,'YourTable'[IDs]
            ,'YourTable'[Value]
        )
        ,'YourTable'[Value]
    )

Is it possible in your source data to have different values for Value for the same IDs? e.g.

IDs	Value
1	5
1	6

If so, what should the result be in this case? Or will that never happen in the data?

[speedramps]

Well done EylesIT. That will work but a SUMMARIZE inside in SUMX is unnecessary CPU effort.

Thanks for helping but this should calc the answer quicker on larger datasets ...

Max value = MAX(yourtable[Value])

Sum of distinct values =

SUMX( DISTINCT(yourtable[IDs]), [Max value])

[Anonymous · Accepted solution]

@speedramps , thanks for the tip about SUMMARIZE being CPU intensive. To re-write the measure without it would be like this:

DistinctValueSum =
    SUMX(
        DISTINCT(
            SELECTCOLUMS(
                'YourTable'
                ,"_IDs", 'YourTable'[IDs]
                ,"_Value", 'YourTable'[Value]
            )
        )
        ,[_Value]
    )