Search code examples
powerbimultiple-columns

PowerBI Filter on several text columns and then produce an average %

I'm trying to take 10 columns that have text in them, filter them all at once based on text, count them, then produce an average.

Text in each column is: On-Site, TW, RDO, Not Set

I have created the following measure.

Monday On-Site = CALCULATE(count('Telework-NEW'[MON]),FILTER(all('Telework-NEW'), [MON] = "On-Site"))

What I don't want to do is do this ten times, then add them together, divide by 10 to get my answer.

I've searched the internet, but I must not be asking this correctly.

Sample Data, attached.enter image description here

The results will be displayed in a card as a percentage.

I hope all this make sense.

Solution

  • Ok, well I figured it out with some additional searching.

    What I did was to create a new table with the data:

    Work Schedule = all ('Telework-NEW'[Name Last], 'Telework-NEW'[Name First],'Telework-NEW'[MON], 'Telework-NEW'[TUES], 'Telework-NEW'[WED], 'Telework-NEW'[THUR], 'Telework-NEW'[FRI], 'Telework-NEW'[MON_2], 'Telework-NEW'[TUES_3], 'Telework-NEW'[WED_4], 'Telework-NEW'[THUR_5], 'Telework-NEW'[FRI_6], 'Telework-NEW'[DOD ID (EDIPI)])

    Then I created a new column that would provide a count of how many times each line had the text "On-Site" in it and divided it by 10, using the following:

    Average # On-Site = (if(('Work Schedule'[MON])="On-Site",1,0)
+if(('Work Schedule'[TUES])="On-Site",1,0)
+if(('Work Schedule'[WED])="On-site",1,0)
+if(('Work Schedule'[THUR])="On-site",1,0)
+if(('Work Schedule'[FRI])="On-site",1,0)
+if(('Work Schedule'[MON_2])="On-site",1,0)
+if(('Work Schedule'[TUES_3])="On-site",1,0)
+if(('Work Schedule'[WED_4])="On-site",1,0)
+if(('Work Schedule'[THUR_5])="On-site",1,0)
+if(('Work Schedule'[FRI_6])="On-site",1,0)) / 10

    I then put this answer in a card.