Sum of text values or convert text to number?

[DSmith4]

Hi, I am trying to sum the dollar amount by vendor.
Our vendor nubmers are alphanumeric so when I do the sum formula I get an error "cannot convert value of type text to type true/false"

Is there a way around this? I am trying to create a column so I can add a page level filter for only vendors that have an amount of 100,000 or greater.

I've tried converting vendor to value but it does not work either.

Formulas and screenshots are below.

Can anyone help?

Vendor Sum = CALCULATE(SUM('GRIR Cleared Items'[Amount in LC]),FILTER('GRIR Cleared Items','GRIR Cleared Items'[Vendor]))

VendorValue = VALUE('GRIR Cleared Items'[Vendor])

[v-yuta-msft · Accepted solution]

@DSmith4 ,

In the first measure, you should add the condition like below:

Vendor Sum =
CALCULATE (
    SUM ( 'GRIR Cleared Items'[Amount in LC] ),
    FILTER ( 'GRIR Cleared Items', 'GRIR Cleared Items'[Vendor] = Condition )
)

About the second measure, please check if the value in column 'GRIR Cleared Items'[Vendor] is text format like "1", "2", "3", etc.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tarunsingla]

You have tried to use the Vendor field in a Filter condition. Filter condition expects the expression to be true/false.

1. In your Vendor SUM calculated measure, drop the filter and instead apply filter in your visual (or Page level or Report Level) on this calculated measure.

2. VALUE function does not work that way. It helps convert numbers (that are stored as text), to numbers.

[DSmith4]

Hi,

This field does not allow me to place in a filter