/datastudio/community?hl=en
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
1 Recommended Answer
$0 Recommended Answers
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!
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
marked this as an answer
Helpful?
-
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
marked this as an answer
Helpful?
This question is locked and replying has been disabled.
Discard post? You will lose what you have written so far.
Write a reply
10 characters required
Failed to attach file, click here to try again.
Discard post?
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?

A problem occurred. Please try again.
Create Reply
Edit Reply
Delete post?
This will remove the reply from the Answers section.
Notifications are off
Your notifications are currently off and you won't receive subscription updates. To turn them on, go to Notifications preferences on your Profile page.
Report abuse
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

Reported post for abuse
Unable to send report.
Report post
Questions about a different Google product are no longer considered off-topic and should be moved to the relevant community.
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

Reported post for abuse
Unable to send report.
This reply is no longer available.
/datastudio/threads
//accounts.google.com/ServiceLogin
You'll receive email notifications for new posts at
Unable to delete question.
Unable to update vote.
Unable to update subscription.
You have been unsubscribed
Deleted
Unable to delete reply.
Removed from Answers
Marked as Recommended Answer
Removed recommendation
Undo
Unable to update reply.
Unable to update vote.
Thank you. Your response was recorded.
Unable to undo vote.
Thank you. This reply will now display in the answers section.
Link copied
Locked
Unlocked
Unable to lock
Unable to unlock
Pinned
Unpinned
Unable to pin
Unable to unpin
Marked
Unmarked
Unable to mark
Reported as off topic
/datastudio/profile/0?hl=en