Sum of column (Score) by ID (dealing with blank values)

[divsforeal]

I have a complex situation. Im trying to calculate sum of Pillar Score by Unique ID (Screenshot below). I want DAX to calculate only the sum of 2 values and display on all rows. (Screenshot below)

Required Output:

Current Output:

DAX Expressions:

Pillar Score:

CALCULATE(AVERAGE('Table'[CC_Pillar_Score]), ALLEXCEPT('Table', 'Table'[Unique ID], 'Table'[Pillar]))

Sum of Pillar Score: (Current Output) 

CALCULATE(SUM('Table'[Pillar Score]), FILTER(ALL('Table'), 'Table'[Unique ID] = EARLIER('Table'[Unique ID])))

Looking for some help to get the required output. Thanks in advance.

[Ashish_Mathur]

Hi,

Try this calculated column formula

=CALCULATE(SUM('Table'[Pillar Score]), FILTER('Table', 'Table'[Unique ID] = EARLIER('Table'[Unique ID])))

Hope this helps.

[ryan_mayu · Accepted solution]

@divsforeal 

pls try this

Measure = sumx(FILTER(all('Table'),'Table'[Unique ID]=max('Table'[Unique ID])),[pillar score2])