Data Studio Case Function Metric/Dimension Error

Question

I have made use of the Case function in Data Studio to ascertain whether a value is over or under £6,000 and present one of two figures dependent on output. These two figures are calculated fields.

First Case statement :

(Over or Under) -

CASE WHEN Cost <= 6000 
THEN 1 
ELSE 0 
END

Second Case statement -

(Total Cost) -

CASE WHEN Over or Under 6,000= 0 
THEN Cost if over 6,000
ELSE Cost if under 6,000
END

Cost if over 6,000 is calculated in the following way -

Cost + (Cost * .1)

and Cost if under 6,000 is calculated -

Cost + 600

This is working perfectly on one report, but when I replicate it in another report with identical data I get the following error message -

An expression can have either metrics, or dimensions, but not both

All calculated fields are defined as numbers so why am I getting this error message? And why would it work in one report and not another?

Sounds odd. The obvious thing to check is that 'Cost', 'Over or Under 6', 'Cost if over 6,000' and 'Cost if over 6,000' are definitely all defined as metrics on both report and have the same default aggregations but I'm guessing you've done that? — Bobbylank
They are all defined as Numbers and are all auto aggregation. These were auto assigned as they are all calculated fields. I just can't work out what the issue is! — Hazel Popham

Taavi Randmaa Taavi Randmaa · Accepted Answer · 2018-12-02T22:30:39

I find Data Studio a bit funny with combining multiple calculated fields. I'd try combining the total into a single calculation.

Cost 
*
CASE WHEN Cost > 6000 THEN 1.1 ELSE 1 END
+
CASE WHEN Cost <= 6000 THEN 600 ELSE 0 END

Sample Report: https://datastudio.google.com/open/1wWrNOgwUG9NGhrjCIof19Hv4Yq9TwBoX

Sample Data: https://docs.google.com/spreadsheets/d/1C_3ZMdQeHnKRkCYfVrlAB_QkI8qKcPfMhLT3QTFS0n4/edit?usp=sharing

Field settings:

Data Studio Case Function Metric/Dimension Error

1 Answers