Google Data Studio Scoreboard SUM calculation

user_18130849 0
Viewed 538

I have my data on Excel and I uploaded in Google Sheets so I can use Google Data Studio, everything in Data Studio so far working good, but I am having trouble in a case.

Few of my fields in Data source have values in numbers from 1 to 1000+ and I chose cell formatting in excel as Numbers with 1000 Separator(,). In Data Studio data source I changed same field with "Number" but when I try to create a simple Scoreboard on Data Studio, it seems like it's only doing SUM with values lower than 1000, meaning any value with 1000 or above are being skipped.

I can feel this is because of separator(,) which is troubling and I can use:

CAST(REGEXP_REPLACE)

but I want to know that why it's making trouble, even after choosing correct Cell Format?

Sample Data Link: https://datastudio.google.com/reporting/abafc2cb-9033-4851-9f72-02896a91384c

google-data-studio
1 Answers

The field ITEM SUPPLIED is a text field and contains numbers formated as text. However, there is a 1000 separator (,) in the text.

Therefore, every , in the field has to be removed. This can be done by the REPLACE function. Then the CAST converts the text to a number.

CAST(REPLACE(ITEM SUPPLIED, ",", "") AS number )
user_165295760
Related