Pie Charts of Aggregate data

[Jeremy Lupoli]

Apologies if this is a simple question as I'm fairly new to non-tabular visualizations.  I've Googled for solutions but I'm not finding it.

I have aggregate data pulled from database queries, which I've placed in a table on my page. It looks something like the following example data.

District	Teachers	Logged In	Not Logged In
Sample District A	16	12	4
Sample District B	14	9	5
Total	30	21	9

Now that I have this, I simply want to create a pie graph of the percent logged in.  To start, for instance, just the following graph for total.

Ultimately, I want to be able to filter by school to see different charts when a different school is selected.

I believe I can do this in data studio w/ Raw data - a record for each and every teacher - but I can't import that much data into Data Studio, as the raw data is much, much bigger than this small example.

I can't find a way via the dimension and metric options to do what I'm looking to do here. Is there a way to create pie charts of aggregate data?

Thanks!
Jeremy

--

Jeremy B. Lupoli, Ph.D. VP of Engineering (202) 491-7340

[Marcos Alcozer]

Hey Jeremy!

This is an issue of wide data vs long data. Take a look at the Data Studio report below.

https://datastudio.google.com/reporting/51ef295a-fc43-4153-ae75-5f22bf2c04e1/page/Ox83C

The dataset you provided had values such as "Logged In" and "Not Logged In" as column names. Really those can be placed under "Login Status". Unpivoting the data so that it is long allows you to roll up the data for a pie chart, but also pivot when you need a data table.

With respect to data size. If you store it in BigQuery, you can store it at a finer grain (by teacher) and roll up in Data Studio without any performance issues. Google will use BI Query Engine to store the data in memory for faster analytics.

Hope this helps!

Marcos

--
You received this message because you are subscribed to the Google Groups "Data Studio in Education" group.
To unsubscribe from this group and stop receiving emails from it, send an email to googleschool-data...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/googleschool-datastudio/CA%2Bk0FAFg2aD5iENT0APhuPndYrwPku9uQ4875%2Bo%2BVVV5A6-djw%40mail.gmail.com.

[Ryan Gravette]

Great way to explain this Marcos!

Quick question, do you guys use specific tools or processes to take the data to unpivot data, or is it just a bunch of click work normally? (Ha, normally, ha, database joke!)

Ryan Gravette
Director of Information and Technology

Idaho Digital Learning Alliance
Work:   208-342-0207
Cell:     208-450-5535
Web:    www.twitter.com/idlagravette

www.idahodigitallearning.org

To view this discussion on the web visit https://groups.google.com/d/msgid/googleschool-datastudio/CAAJZMmziFVysoU9OcDrD3MLy0jyWngPF9tj5ynTGW7XEHjCpjA%40mail.gmail.com.

---

This communication is subject to IDLA's Privacy Policy and Email Disclaimer.

[Jeremy Lupoli]

Thanks Marcos, figured that might be the case. Will try it out with the raw data.

To view this discussion on the web visit https://groups.google.com/d/msgid/googleschool-datastudio/CAAJZMmziFVysoU9OcDrD3MLy0jyWngPF9tj5ynTGW7XEHjCpjA%40mail.gmail.com.

[Chris Smith]

Ryan,

I typically use the following formula to unpivot data: 

=ARRAYFORMULA(split(flatten(transpose(query(transpose(A2:C1000&"|"),,9^9))&"|"&D2:L1000),"|"))

Of course, this is done in Google Sheets where we can use the FLATTEN formula.  The first range, A2:C1000 is the range of data that you want to be repeated on each row. (Student IDs, Names, etc) The second range D2:L1000 is the data that you would like split and then flattened into a single column. Test scores, for example.

Ben Collins also has a nice tutorial on a similar, but slightly different way of going about this: https://www.benlcollins.com/spreadsheets/flatten-function/

Best,

Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/googleschool-datastudio/CAJgeW7CBZU9FaReJzB8OpwvLxO-dsLu%3DTsoBk2F9J8U%3DVegGYQ%40mail.gmail.com.

--

Singapore American School

Chris Smith | Data & Logistics Team: Academic Data Specialist |  APLS

 Apple Distinguished Educator |  Apple Certified Teacher | Google Certified Innovator

40 Woodlands Street 41 | Singapore 738547

Phone: +65 6360 3406 ext 6165

---

CONFIDENTIALITY CAUTION: This message is intended only for the use of the individual or entity to whom it is addressed and contains information that is privileged and confidential. If you, the reader of this message, are not the intended recipient, you should not disseminate, distribute or copy this communication. If you have received this communication in error, please notify us immediately by return email and delete the original message. Unless it relates to the official business of Singapore American School, any opinions or matters expressed in this message are those of the individual sender.

[Marcos Alcozer]

On the BigQuery side, Google released an unpivot operator that works really well:

https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unpivot_operator

Marcos

To view this discussion on the web visit https://groups.google.com/d/msgid/googleschool-datastudio/CAJgeW7CBZU9FaReJzB8OpwvLxO-dsLu%3DTsoBk2F9J8U%3DVegGYQ%40mail.gmail.com.