Google Data Studio中的COUNTIFS功能

问题描述

我有2个列，分别称为大洲和完成度，我想根据以下数据计算每个大洲的完成率.这是在excel中使用COUNTIFS()函数定义的

I have 2 columns called continent and completion and i would like to calculate completion % for each continent based on below data.This was defined in excel using COUNTIFS() function

我们可以在Data Studio中实现吗?

Can we achieve this In data studio??

完成百分比公式为已关闭"中的状态总数/状态总数状态值为打开"，进行中"，服务完成"，关闭"

Completion % formula is Total number of Status in 'closed' / Total number of StatusStatus values are 'open','in progress','service completed','closed'

continent          completion %

------------     -------------------

Asia                  97%(used countifs function to calculate percentage in excel)
Europe                89%
North America         93%

推荐答案

据我了解，您将需要一个名为"Closed"的新字段，并且该代码应类似于:

From what I understand you will need a new field called 'Closed' and that code should look something like:

CASE
   WHEN [Whatever your status field name is] = 'closed' THEN 1
   ELSE 0
END

然后，您将不得不在另一个新字段中使用该字段，即封闭的总额"，该字段应类似于:

And then you will have to utilize that in another new field called 'Total closed' that should look like:

SUM(CASE
      WHEN Closed = 1 THEN 1
      ELSE 0
    END)

然后从那里可以创建完成百分比":

And then from there you can create 'Completion %':

Total closed / COUNT([Your status field])

此处是链接一个可能有用的Google论坛问题

Here is a link to a google forum question that might be useful

这篇关于Google Data Studio中的COUNTIFS功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！