Calculated Column based on Column Value

[Jadegirlify]

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

[Jadegirlify]

@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.

[rajendraongole1 · Accepted solution]

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!!

[Jadegirlify]

@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

[Jadegirlify]

@rajendraongole1 Do you happen to had a mesure to obtain the follow columns above: Total Department, Onsite, Virtual and Total.

[Jadegirlify]

@rajendraongole1 That worked! Thanks so much for the prompt response.

[rajendraongole1]

@Jadegirlify Thanks for confirming and Happy to Help!!

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[rajendraongole1]

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!!