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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
malpani
Helper I
Helper I

Sum a column in a table based on unique IDs

Hello Experts, 

 

I have following data table. I have a requirement to calculate SUM of Approval Age (days) columns based on unique values in ID column. 

In table below, it should consider only one rows out of two rows with ID 51921.

 

IDCountcFeatureNameApproval Age (days)
WTD Live-67801Feature 18
WTD Live-67861Feature 28
WTD Live-67821Feature 38
WTD Live-67891Feature 48
WTD Live-12331Feature 591
WTD Live-51921Feature 6224
WTD Live-51922Feature 6224

 

I tried referencing other threads but somehow it is not working and showing error with syntax. 

 

Sum DISTINCT = 

CALCULATE(SUM('WTD+SP_Pre-C3_Final'[Approval Age (days)]), FILTER('WTD+SP_Pre-C3_Final', DISTINCT('WTD+SP_Pre-C3_Final'[ID])))
 
Error Message - 

A table of multiple values was supplied where a single value was expected.

 

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @malpani 
Please use

Sum DISTINCT =
SUMX (
    VALUES ( 'WTD+SP_Pre-C3_Final'[ID] ),
    CALCULATE ( MAX ( 'WTD+SP_Pre-C3_Final'[Approval Age (days)] ) )
)

View solution in original post

1 REPLY 1
tamerj1
Super User
Super User

Hi @malpani 
Please use

Sum DISTINCT =
SUMX (
    VALUES ( 'WTD+SP_Pre-C3_Final'[ID] ),
    CALCULATE ( MAX ( 'WTD+SP_Pre-C3_Final'[Approval Age (days)] ) )
)

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors