CODEWITHSUNDEEP
looker-studio
user9203730
user9203730

Reputation:

COUNTIFS function in Google Data studio

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

Can we achieve this In data studio??

Completion % formula is Total number of Status in 'closed' / Total number of Status Status 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%

Upvotes: 6

Views: 58735

Answers (1)

Dakota Maker
Dakota Maker

Reputation: 633

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])

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

Upvotes: 21

Related Questions