cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DSmith4
Helper II
Helper II

Sum of text values or convert text to number?

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])
 
2019-10-23_12-43-40.png
 
2019-10-23_12-41-55.png
1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

@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.

 

View solution in original post

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

@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.

 

View solution in original post

tarunsingla
Solution Sage
Solution Sage

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.

Hi,

 

This field does not allow me to place in a filter

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.