CODEWITHSUNDEEP
looker-studio
Codist
Codist

Reputation: 769

How to sum up values of two category and show in a column chart in Google Data Studio?

My data look like this.

There are three branches, ABC, PQR, and XYZ and each one do three types of businesses, Counter Sale, Online Sale and Van sale. I know how to show this data in a column chart as it is, but my requirement is, I want to sum up the counter sale and the van sale by branch and date, let's call it ABC - Offline Sale, PQR - Offline Sale and XYZ - Offline Sale and then show this in a column chart. I have been after this for two days and couldn't find any help online. I tried to create calculated fields using case statements but it didn't work as expected and I couldn't really accomplish what I wanted.

Date Branch SALE PROFIT
01-Jan-23 ABC - COUNTER SALE 184518 184518
01-Jan-23 ABC - VAN SALE 0 0
01-Jan-23 ABC - ONLINE 0 0
01-Jan-23 XYZ - COUNTER SALE 144425 144425
01-Jan-23 XYZ - VAN SALE 38163 38163
01-Jan-23 XYZ - ONLINE 0 0
01-Jan-23 PQR - COUNTER SALE 261219 261219
01-Jan-23 PQR - VAN SALE 0 0
01-Jan-23 PQR - ONLINE 53851 53851
02-Jan-23 ABC - COUNTER SALE 280046 280046
02-Jan-23 ABC - VAN SALE 97992 97992
02-Jan-23 ABC - ONLINE 0 0
02-Jan-23 XYZ - COUNTER SALE 276917 276917
02-Jan-23 XYZ - VAN SALE 15815 15815
02-Jan-23 XYZ - ONLINE 0 0
02-Jan-23 PQR - COUNTER SALE 310918 310918
02-Jan-23 PQR - VAN SALE 0 0
02-Jan-23 PQR - ONLINE 129857 129857
03-Jan-23 ABC - COUNTER SALE 197445 197445
03-Jan-23 ABC - VAN SALE 39976 39976
03-Jan-23 ABC - ONLINE 0 0
03-Jan-23 XYZ - COUNTER SALE 206751 206751
03-Jan-23 XYZ - VAN SALE 24715 24715
03-Jan-23 XYZ - ONLINE 0 0
03-Jan-23 PQR - COUNTER SALE 303617 303617
03-Jan-23 PQR - VAN SALE 0 0
03-Jan-23 PQR - ONLINE 189570 189570
04-Jan-23 ABC - COUNTER SALE 203264 203264
04-Jan-23 ABC - VAN SALE 84817 84817
04-Jan-23 ABC - ONLINE 30793 30793
04-Jan-23 XYZ - COUNTER SALE 214984 214984
04-Jan-23 XYZ - VAN SALE 23149 23149
04-Jan-23 XYZ - ONLINE 0 0
04-Jan-23 PQR - COUNTER SALE 287748 287748
04-Jan-23 PQR - VAN SALE 0 0
04-Jan-23 PQR - ONLINE 540781 540781
05-Jan-23 ABC - COUNTER SALE 219093 219093
05-Jan-23 ABC - VAN SALE 44327 44327
05-Jan-23 ABC - ONLINE 104569 104569
05-Jan-23 XYZ - COUNTER SALE 167817 167817
05-Jan-23 XYZ - VAN SALE 96145 96145
05-Jan-23 XYZ - ONLINE 0 0
05-Jan-23 PQR - COUNTER SALE 297488 297488
05-Jan-23 PQR - VAN SALE 0 0
05-Jan-23 PQR - ONLINE 348895 348895

Upvotes: 0

Views: 171

Answers (1)

rockinfreakshow
rockinfreakshow

Reputation: 30240

you can create a calculated field as such:

REGEXP_REPLACE(Branch,"VAN|COUNTER","OFFLINE")

enter image description here

Upvotes: 1

Related Questions