Adding totals based on titles

Reply
Highlighted
Yellow Belt

Adding totals based on titles

hi,

 

I am trying to group together user totals based on their adjacent column title I have tried groups and cases but cant get this to work.  My latest attempt is below, can anyone help.

 

SUM(
CASE
when `PS Variable Stub`="ADenabled" or `PS Variable Stub`="O365enabled" then `GUID_ User Count`
else 0
End
)

 

Thanks


Accepted Solutions
Major Brown Belt

Re: Adding totals based on titles

You seem to have been on the right track, I didn't really need to do anything different than you did.

 

Here are my results based on your sample data

Dojo Help 200.JPG

 

 

Total Active Accounts:

SUM(
  (CASE 
  WHEN `PS Variable Stub` = 'TOTALADaccounts' OR `PS Variable Stub` = 'totalO365accounts' THEN `UPN: User Count`
 
  ELSE 0
  END)
  )

No MFA:

SUM(
  (CASE 
  WHEN `PS Variable Stub` = 'EnabledInMFAExclude' OR `PS Variable Stub` = 'O365MFANotOnAccountRequired' OR `PS Variable Stub` = 'O365MFAExclude' OR `PS Variable Stub` = 'O365MFANotOnAccountRequired' THEN `UPN: User Count`
 
  ELSE 0
  END)
  )

Internet Blocked Accounts:

SUM(
  (CASE 
  WHEN `PS Variable Stub` = 'TOTALADaccounts' OR `PS Variable Stub` = 'totalO365accounts' THEN `UPN: User Count`
 
  ELSE 0
  END)
  )
  
  -
  
  SUM(
  (CASE 
  WHEN `PS Variable Stub` = 'EnabledInMFAExclude' OR `PS Variable Stub` = 'O365MFANotOnAccountRequired' OR `PS Variable Stub` = 'O365MFAExclude' OR `PS Variable Stub` = 'O365MFANotOnAccountRequired' THEN `UPN: User Count`
 
  ELSE 0
  END)
  )
  
  -
  
  SUM(
    (CASE 
    WHEN `PS Variable Stub` = 'EnabledWithMFA' THEN `UPN: User Count`
     ELSE 0
    END)
   )
    

All Replies
Major Brown Belt

Re: Adding totals based on titles

Can you provide any sample data that I can test with? And the numbers you expect to see?

Yellow Belt

Re: Adding totals based on titles

hi,

 

thanks, basically our of AD i can get a number of AD/O365 I can get totals but need to merge some together into totals i.e.

 

TOTALADaccounts+totalO365accounts=total Active Accounts

 

EnabledInMFAExclude+O365MFANotOnAccountRequired+O365MFAExclude+O365MFANotOnAccountRequired=No MFA

 

total Accounts-NoMFA-EnabledWithMFA=Internet blocked accounts

 

PS Variable StubUPN: User Count
TOTALADaccounts912
EnabledInMFAExclude2
EnabledWithMFA281
O365MFANotOnAccountRequired1
totalO365accounts39
O365MFAExclude34
O365MFANotOnAccountRequired5

 

Numbers and titles have been randomised but you get the jist

 

thanks

Major Brown Belt

Re: Adding totals based on titles

You seem to have been on the right track, I didn't really need to do anything different than you did.

 

Here are my results based on your sample data

Dojo Help 200.JPG

 

 

Total Active Accounts:

SUM(
  (CASE 
  WHEN `PS Variable Stub` = 'TOTALADaccounts' OR `PS Variable Stub` = 'totalO365accounts' THEN `UPN: User Count`
 
  ELSE 0
  END)
  )

No MFA:

SUM(
  (CASE 
  WHEN `PS Variable Stub` = 'EnabledInMFAExclude' OR `PS Variable Stub` = 'O365MFANotOnAccountRequired' OR `PS Variable Stub` = 'O365MFAExclude' OR `PS Variable Stub` = 'O365MFANotOnAccountRequired' THEN `UPN: User Count`
 
  ELSE 0
  END)
  )

Internet Blocked Accounts:

SUM(
  (CASE 
  WHEN `PS Variable Stub` = 'TOTALADaccounts' OR `PS Variable Stub` = 'totalO365accounts' THEN `UPN: User Count`
 
  ELSE 0
  END)
  )
  
  -
  
  SUM(
  (CASE 
  WHEN `PS Variable Stub` = 'EnabledInMFAExclude' OR `PS Variable Stub` = 'O365MFANotOnAccountRequired' OR `PS Variable Stub` = 'O365MFAExclude' OR `PS Variable Stub` = 'O365MFANotOnAccountRequired' THEN `UPN: User Count`
 
  ELSE 0
  END)
  )
  
  -
  
  SUM(
    (CASE 
    WHEN `PS Variable Stub` = 'EnabledWithMFA' THEN `UPN: User Count`
     ELSE 0
    END)
   )
    
Yellow Belt

Re: Adding totals based on titles

Great thanks, Looks like I was mainly missing some brakets!

Announcements
Check out these great solution videos! The new iSolveditWithDomoboard is live, share yours and earn higher rankings in Dojo click here!