Forum Discussion
Calculated Column based on Column Value
I need to create a calculation showing departments counts of employees working onsite vs virtual. I tried several measures/calculated column but I was stuck at the "Work Location Type" column. It won't do a distinct count of the departments. Please see the result table I am trying to achieve. The last column uses the following criteria:
If Onsite is 0 and Virtual >0 return "Virtual"
If Onsite >0 and Virtual = 0 return "Onsite"
If Onsite > 0 and Virtual > 0 return "Onsite & Virtual "
Original Table
| Department | Work Location |
| HR | Onsite |
| HR | Onsite |
| HR | Onsite |
| HR | Onsite |
| Claims | Onsite |
| Claims | Virtual |
| Underwriting | Onsite |
| Underwriting | Onsite |
| Underwriting | Virtual |
| Underwriting | Virtual |
| Underwriting | Virtual |
| Operations | Virtual |
| Operations | Virtual |
Result Table
| Department | Onsite | Virtual | Total Onsite/Virtual | Work Location Type |
| HR | 4 | 0 | 4 | Onsite |
| Claims | 1 | 1 | 2 | Onsite & Virtual |
| Underwriting | 2 | 3 | 5 | Onsite & Virtual |
| Operations | 0 | 2 | 2 | Virtual |
Any assistance will be greatly appreciated
Hi Jadegirlify - Yes you can create it by using below measures and display the output in table chart from same original table.
Measure for Onsite Count:
Onsite Count =
CALCULATE(
COUNT('OriginalTable'[Work Location]),
'OriginalTable'[Work Location] = "Onsite"
) +0Measure for Virtual Count:
Virtual Count =CALCULATE(COUNT('DWLP'[Work Location]),'DWLP'[Work Location] = "Virtual")+0Measure for Total Onsite/Virtual:Total Onsite/Virtual = [Onsite Count] + [Virtual Count]Measure for Work Location Type:Work Location Type =SWITCH(TRUE(),[Onsite Count] = 0 && [Virtual Count] > 0, "Virtual",[Onsite Count] > 0 && [Virtual Count] = 0, "Onsite",[Onsite Count] > 0 && [Virtual Count] > 0, "Onsite & Virtual")Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
7 Replies
Hi Jadegirlify - Create a calculated Table
Output =SUMMARIZE('DWLP','DWLP'[Department],"Onsite", CALCULATE(COUNTROWS('DWLP'), 'DWLP'[Work Location] = "Onsite"),"Virtual", CALCULATE(COUNTROWS('DWLP'), 'DWLP'[Work Location] = "Virtual"))create two calculated columns , one for to calculate Total Online/Virtual and another worklocation using switch statements.
Total Onsite/Virtual = [Onsite] + [Virtual]another calculated Column:Work Location Type =SWITCH(TRUE(),[Onsite] = 0 && [Virtual] > 0, "Virtual",[Onsite] > 0 && [Virtual] = 0, "Onsite",[Onsite] > 0 && [Virtual] > 0, "Onsite & Virtual")Try the above logic and let knowDid I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!rajendraongole1 Thanks it worked. Is there a way to achieve these calculations in the original table without creating a calculated table? I am unable to connect it to the original table and I need a bunch of filters from the original table.
Hi Jadegirlify - Yes you can create it by using below measures and display the output in table chart from same original table.
Measure for Onsite Count:
Onsite Count =
CALCULATE(
COUNT('OriginalTable'[Work Location]),
'OriginalTable'[Work Location] = "Onsite"
) +0Measure for Virtual Count:
Virtual Count =CALCULATE(COUNT('DWLP'[Work Location]),'DWLP'[Work Location] = "Virtual")+0Measure for Total Onsite/Virtual:Total Onsite/Virtual = [Onsite Count] + [Virtual Count]Measure for Work Location Type:Work Location Type =SWITCH(TRUE(),[Onsite Count] = 0 && [Virtual Count] > 0, "Virtual",[Onsite Count] > 0 && [Virtual Count] = 0, "Onsite",[Onsite Count] > 0 && [Virtual Count] > 0, "Onsite & Virtual")Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!rajendraongole1 That worked! Thanks so much for the prompt response.
