Forum Discussion
Calculated Column based on Column Value
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 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.
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 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.