6

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%
piet.t
  • 11,718
  • 21
  • 43
  • 52

1 Answers1

21

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

Dakota Maker
  • 633
  • 4
  • 24