Forum Discussion

malpani's avatar
malpani
Icon for Helper II rankHelper II
4 years ago
Solved

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.

 

 

  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi malpani 
    Please use

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

1 Reply

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi malpani 
    Please use

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