Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM. Register now.

Reply
Anonymous
Not applicable

Sum of Distinct ID with multiple Distinct values

 

My data:

 

2019-02-21_14-54-31.png


Kindly help!

1 ACCEPTED SOLUTION

@Anonymous 

 

In that case

 

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

 

View solution in original post

12 REPLIES 12
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

Try

 

Column =
SUMX ( VALUES ( [AMOUNT] ), [Amount] )
Anonymous
Not applicable

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

 

 

 

 

@Anonymous 

 

In that case

 

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

 

Anonymous
Not applicable

@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]
)

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
Not applicable

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! 

 

@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

 

 

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

Anonymous
Not applicable

@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
Not applicable

@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.

@Anonymous 

 

I will get back to you.

Anonymous
Not applicable

@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!

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.