Data Studio
4.9K members online now
4.9K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

V-Lookup Style Data Retrieval

Follower ✭ ✭ ✭
# 1
Follower ✭ ✭ ✭

With the all new Case Metrics, I'm trying to assign a value to a set of words. In this case it's Australian Payroll Tax Amounts. Which are as follows;  NSW = 5.45%, VIC = 4.85%, ACT = 6.85% and so on...

 

I have a Field called 'States' which lists the Australian State the person is working in as a Dimension, either NSW or VIC or ACT etc.  What is the easiest way to a assign the corresponding percentage Tax % value to that Dimension?

 

The only way I see to do it, not mixing Dimensions & Metrics is as follows, but seem very laborious. Am I missing a trick here?

 

Function: 'NSW Payroll Tax'      Case When 'States = "NSW" Then 1 Else 0 End
Function: 'NSW Payroll Tax Amount'      NSW Payroll Tax * 5.45/100
Function: 'VIC Payroll Tax'       Case When States = "VIC" Then 1 Else 0 End

Function: 'VIC Payroll Tax Amount'        VIC Payroll Tax * 4.85/100

 

.....And so on for every State in Australia.  Are there any shortcuts I am missing???

V-Lookup Style Data Retrieval

Explorer ✭ ✭ ☆
# 2
Explorer ✭ ✭ ☆

Paul, as of now, the easiest way is to add a column with the tax % in our datasets at the source level.

Re: V-Lookup Style Data Retrieval

Follower ✭ ✭ ✭
# 3
Follower ✭ ✭ ✭

Easiest way initially, but we get a new CSV every week.  So having all the functionality in Data Studio would save time in the long run as you can just upload the CSV.  Just wondering if you can optimise the Case Functions?

V-Lookup Style Data Retrieval

Explorer ✭ ✭ ✭
# 4
Explorer ✭ ✭ ✭

Hi @Paul A,

 

Not sure on your output, so this may or may not be suitable for you, but I have used something similar to pull in budget figures matched to the current date - you have to put the data into a separate data source (Google Sheets in my example) then connect it in, but you would be able to tweak it to match dimensions to a dynamic metric.

 

Here's my blog post on this subject:

http://singularbean.co.uk/bullet-charts-with-a-dynamic-goal-in-google-data-studio/

 

Hope that helps,

Ben

V-Lookup Style Data Retrieval

Explorer ✭ ✭ ☆
# 5
Explorer ✭ ✭ ☆

i don't use data studio for production, but I find the best way is to do my data transformation outside data studio and use it only for charts

V-Lookup Style Data Retrieval

Follower ✭ ✭ ✭
# 6
Follower ✭ ✭ ✭

Thank you for your posts, but this is about Data studio's new metric case functionality and what it can do. I've managed to get it to translate 6 dimensions into metrics. Who else is pushing its functionality and is my 1 0, yes / no example the best way? Can this be shortened with conditional case functionality?