SUM of distinct values

[efroseroc]

Hi thank you for reading,

I have the following structure:

ID	Income
A1	100
A1	100
A2	100
A3	200
A3	100
A3	200

The result I want is: A1=100
                                A2=100

                                A3=300

So, for example if A1 has the same value 2 times I want the result to be the same value: 100

and if A3 has two 200s and one 100 I want the sum of the distinct values: 300

I also have another table with just:

ID

A1

A2

A3

Thanks for your help,

[parry2k]

@efroseroc use the one which gives you the correct result, simple 🤷‍♀️

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@efroseroc my dax expression should have worked without doing checking HASONEVALUE but good to hear that you found the solution.

[efroseroc]

Thanks @parry2k, I found the @Jihwan_Kim expresion easier to undestand for me.

But now that you mentioned it both expressions give me different totals but the same values.

The one on the right is the measure that doesnt use HASONEVALUE

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please try the below.

[efroseroc]

hi @Jihwan_Kim, thank you I think managed to use your code to adapt to my situation. Please correct me if I'm wrong

Measure = IF(HASONEVALUE(Data[Income]),
                MAX(Data[Income]),
                SUMX(SUMMARIZE(Data,Data[Income]),Data[Income])
)

So if we have just one income value per ID , take the max. That would be the case of A1 where we have 100, 3 times so the result is 100.

If we have more than one income value per ID as is the case of A3 where we have [200,200,100] , sum the distinct values so 200+100=300.

Thanks again ! you saved me a headache 

[parry2k]

@efroseroc try this measure

SUM = 
SUMX ( 
    SUMMARIZE (
        'Table (3)',
        'Table (3)'[ID],
        'Table (3)'[Income], 
        "@Income", MAX ( 'Table (3)'[Income] ) 
    ), 
    [@Income] 
)

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[soriyavithya]

Your method works for me somehow... Thanks a lot!!