Forum Discussion

Jadegirlify's avatar
Jadegirlify
Helper I
2 years ago
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...
Need Help
Tips and Tricks
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    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"
    ) +0

     

    Measure for Virtual Count:

     

    Virtual Count =
    CALCULATE(
        COUNT('DWLP'[Work Location]),
        'DWLP'[Work Location] = "Virtual"
    )+0
     
     Measure 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's avatar
rajendraongole1
Super User
2 years ago

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 know
 
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!