cancel
Showing results for
Did you mean:

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

Frequent Visitor

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

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.
1 ACCEPTED SOLUTION
Super User

pls try this

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

Proud to be a Super User!

2 REPLIES 2
Super User

Hi,

Try this calculated column formula

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

pls try this

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

Proud to be a Super User!