Sum excluding NAs and applying a condition

[Chris123Terr]

I have an age column that is in text format because it has NA.
I want to add those values ​​that are greater than 50, but I need to exclude the NAs. How could I do it? Thanks.

[parry2k]

@Chris123Terr you can tweak the measure

Measure = 
CALCULATE ( 
   SUM ( IFERROR ( Table[Column] ), 0 ),
   IFERROR ( VALUE ( Table[Age] ), 0 ) > 50 
)

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[MahyarTF]

Hi,

If I am not mistaken, you want to exclude the NA values,

In Power query you could change the datatype of the column to number then do one of belows :

1- Remove the Error rows :

2- Replace the error value with nul or whatever you want :

this is the result (for second way) :

Appreciate your Kudos

[Chris123Terr]

It's a good suggestion too. Thank you so much.

[MahyarTF · Accepted solution]

Thanks for your Kudos and please mark my post as a solution if you like it

[parry2k]

@Chris123Terr If I remember it correct I did answer this question yesterday

Measure = 
CALCULATE ( 
   SUM ( Table[Column] ),
   IFERROR ( VALUE ( Table[Age] ), 0 ) > 50 
)

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Chris123Terr]

Yes. It happens that with that DAX function I get the following error: "The Sum function does not admit values ​​of type string". This seems to me to mean that the "SUM ( athlete_events_NEW[Age] )" part of the sum, has the NA values.
On the other hand, when I do it with the "COUNTROWS" function, that problem does not happen.

Años_Distinct = CALCULATE (

SUMX ( table , IFERROR ( VALUE ( table[Age] ) , 0 ) ) ,

IFERROR ( VALUE ( table[Age] ), 0 ) > 50

)

With this new measurement I get the correct values, but it seems very long for something so simple.