How can I count the number of values accross all columns in a table?

[Thinker77]

I have many different surveys with their results in different tables but using the same scale (good, bad, ok, for example).  I want to count all the goods, bads and oks accross a table and then collate my results from all tables where I can see the results over time.  I tried using a formula like this: New Teacher Negative = COUNTAX('New Teachers', "Needs Improving") + COUNTAX('New Teachers', "Unsatisfactory") but it counts every row.  Surely there must be a way to do this fairly easily without adding a count from each column (some of the surveys are quite long).

Any suggestions?

[Greg_Deckler]

Some sample data would help, but you might create a central table with your survey answers using an Enter Data query to enter "good", "bad", "ok" or whatever, relate all of your survey tables to this central table and then create a custom column kind of like:

Count = COUNTAX('Table'[Column],RELATEDTABLE('Table')) + COUNTAX('OtherTable'[Column],RELATEDTABLE('OtherTable')) + ...

[Thinker77]

Hi,

Thanks for the idea.  I am planning on refreshing the data source (a collection of Excel files dumped out of Survey Monkey) in the future so I don't want to do any that would require someone else to need to rework the data afterwards.  Also, all the data is meaningful related (through collectors).  Here is a sample of the data I am working with:

I have 5 tables full of data, all related to each other and all with answers that follow a very predictable format like this.  Essentially I want to aggregate all the results so I can see if all the data from all the surveys points in the same direction.  On the dashboard, I want to have slicers that allow the viewer to effectively analyze the results by filtering out certain academic years or cohort types.  I want to count all the "high satisfactory" and "good" results from a whole table without necessarily having a count for each column (as there are many and they have crazy names that happen to be the survey question - which is useful).

I hope that clarifies things.  Any more ideas?  Thanks in advance!

[v-huizhn-msft · Accepted solution]

Hi @Thinker77,

If all your five table have same columns and the format is same, please Append them to one table by using Append Queries in Query Edit.



Then create a calculated column to get the year, create measure to calculate the count of each type using the formula.

Year = YEAR(Table4[Date])

test = COUNTA(Table4[Apply Interview])

Create two slicers including Year and Apply Interview fields. Create a table used to display the result.



When you select diferent year and Apply interview,it will display different result.

In addition, if you want to count the number of values across all columns in a table. Please unpivot the columns except date field.

For the following table, you want to calculate the count the number of values for “Apply interview” and “Communication” columns. You can right click the table-> Edit Query, select the “Apply interview” and “Communication” columns->Unpivot columns under transform, you will get the following table.

Finnaly, you can use the new column(highlighted in wire frame) and the solution above to calculate the desired result.



If you have other issues, don't hesitate to let me know.

Best Regards,
Angelia