Sum of Distinct ID with multiple Distinct values

[Anonymous]

My data:

Kindly help!

[Zubair_Muhammad]

@Anonymous 

Try

Column =
SUMX ( VALUES ( [AMOUNT] ), [Amount] )

[Anonymous]

Thank you Zubair.  But it give me sum of including all other ids.  I need sum of Distinct values per distinct id.

For example

ID           Amount      Sum

1             10           30

1             20           30

1             20           30

1             10           30

2             5             5

2             5             5

2             5             5

3             10           45

3             5             45

3             10           45

3             30           45

3             5             45

[Zubair_Muhammad · Accepted solution]

@Anonymous 

In that case

=
SUMX (
    CALCULATETABLE ( VALUES ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[ID] ) ),
    [Amount]
)

[Anonymous]

@LivioLanzo Would you please help me understand this dax - how it is performing the calculation?

=
SUMX (
    CALCULATETABLE ( VALUES ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[ID] ) ),
    [Amount]
)

[Zubair_Muhammad]

Hi @Anonymous 

I am so sorry. I missed your notification earlier

This is how it works. Following table creates a Single Column Table Containing distinct Values (for each ID)

CALCULATETABLE ( VALUES ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[ID] )

Then we use an ITERATOR function (X function) to sum the values of this table

So
SUMX(previoustable,ColumntoSum)

[Anonymous]

Thank you so much @Zubair_Muhammad .

So  ALLEXCEPT ( Table1, Table1[ID] ) tells the formula to return distinct values for each ID.  If I use any other column e.g., Type in place of ID then the overall calculation will bring distinct values for each Type?   Am i understanding it correctly?

Thank you once again for all your time and help! 

[Zubair_Muhammad]

@Anonymous 

Allexcept returns a table with all filters removed except for the filters on the specified columns.

So for each ID (each row) you get a filtered table containing all rows with the same ID.

Then CALCULATETABLE takes the distinct values from that Filtered Table

Here are some good REFERENCES for allexCEPT

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

https://docs.microsoft.com/en-us/dax/allexcept-function-dax

[mattlewis]

thanks so much - this explanation really helped.  Using CALCULATETABLE to reduce the table to just one row per duplicate ID is great!

[Anonymous]

@Zubair_Muhammad  Thank you so so much!  

What is the easiest way to see the actual data of each row that is beaing calculated by formula: 

CALCULATETABLE ( VALUES ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[ID] )

I mean, is there any way I can practiacally see the - filtered table containing all rows with the same ID for each ID (each row)?  

I understand the logic, but I want to play with different combinations so want to see the actual data that is being returned by this formula in my data model.

Kindly advice!  Thank you again for all your time and knowledge sharing.

[Anonymous]

@Zubair_Muhammad  Please help me with above qquestion.  I think what I am trying to ask, how can I debug nmy dax when it returns a table of data rather than scaler value for particular row item.  Please read my above post as well.

Thank you for all kind help.

[Zubair_Muhammad]

@Anonymous 

I will get back to you.

[Anonymous]

@Zubair_Muhammad  Thank you so much Sir!  It seems to be working :).  Would you please help me understand this formula? 

Appreciate your kind help!

Thank you again!