Forum Discussion
Solved
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...
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 Also with the same table, I would like to have this output table:
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 (Total HR count is 4 and all 4 are onsite, that's why virtual is 0 etc)
| Department | Total Department | Work Location | Onsite | Virtual | Total |
| HR | 13 | Onsite | 4 | 0 | 4 |
| HR | 13 | Onsite | 4 | 0 | 4 |
| HR | 13 | Onsite | 4 | 0 | 4 |
| HR | 13 | Onsite | 4 | 0 | 4 |
| Claims | 13 | Onsite | 1 | 0 | 2 |
| Claims | 13 | Virtual | 0 | 1 | 2 |
| Underwriting | 13 | Onsite | 2 | 0 | 5 |
| Underwriting | 13 | Onsite | 2 | 0 | 5 |
| Underwriting | 13 | Virtual | 0 | 3 | 5 |
| Underwriting | 13 | Virtual | 0 | 3 | 5 |
| Underwriting | 13 | Virtual | 0 | 3 | 5 |
| Operations | 13 | Virtual | 0 | 2 | 2 |
| Operations | 13 | Virtual | 0 | 2 | 2 |
rajendraongole1 Do you happen to had a mesure to obtain the follow columns above: Total Department, Onsite, Virtual and Total.