This content is likely not relevant anymore. Try searching or browse recent questions.
-
12/9/19
calculation of average values ignoring empty rows 1 Recommended Answer 3 Replies 0 Upvotes
I have a question concerning the calculation of average values in data studio.

In Google Sheets we have sales data for a whole year, each day containing the cumulative data of that day. We want to be able to calculate the average daily profit per item sold.

Problem is, that many rows are empty, as the company was closed on that day an did not sell anything. So caluclating the average value on the whole year would only be correct, if we could extract those rows with a value greater than zero.

Currently we calculate it like this:

(SUM(SALES_REV) / SUM(SALES_ITEMS)) / days of selected timerange

SALES_REV = the revenue of all sales
SALES_ITEMS = the number of items sold
The numbers of rows used to calculate the overall average of the selected timespan is defined by datastudio according to rows (days) in google sheets.

But we rather need something like this:

IF SALES_REV > 0
-> Take this day (row) into the calculation
ELSE
-> ignore this day (row)

So if we have 365 rows of data, but only 295 have a value greater than zero, data studio should calulate the result:

(SUM(SALES_REV) / SUM(SALES_ITEMS) / 295 (instead of 365).

Is this calculation possible in data studio?

Thanks a lot for some help!
Details
All Replies (3)
12/9/19
Hey Blitz Donner

Created a Google Data Studio Report (Google Sheets Embedded) to demonstrate, as well as a GIF showing the process below.

1) Revenue per Item
`SUM(SALES_REV) / SUM(SALES_ITEMS)`

2) Rev per Item per Day
`SUM(SALES_REV) / SUM(SALES_ITEMS) / COUNT(Date)`

3) Filter out SALES_REV = 0
`Exclude SALES_REV Equals to (=) 0`

Hope this helps

Last edited 12/9/19
-
12/9/19
Thanks a lot, that's pretty helpful !!

One more question:
If I want to count the number of rows instead of the numbers of days (count(date)). Is there a function for this as well? I realized, that for certain dates there is more than one row available.
Last edited 12/9/19
This question is locked and replying has been disabled.
10 characters required
You will lose what you have written so far.
Personal information found

We found the following personal information in your message:

This information will be visible to anyone who visits or subscribes to notifications for this post. Are you sure you want to continue?

Delete post?