Multiple Columns

[NJ13]

Hello All,

I'm struggling with the following. I have different columns which shows various labels with different categories.  Here is an example of data. i have deliminated the data in PowerBI and i looks like this.

i am trying to do a stacked bar and i have tried almost everything to figure out  a way to get a total count of each category across all columns withougth duplicating or giving inconsistent numbers or giving couple answers for each category. is there a way i can get one for each and add them together (sum) and automate that process for repetitveness ?

thank you so much for your help

[NJ13]

Hello,

   i have a question in regards to Multiple columns... i have 34 rows with 14 different columns with different text categories  (e.g cleate, jerseys, socks, shorts, and pants) all 14 categories/columns where transforned by delimination.... i need to find counts of each the categories.. is there a way of going about it without adding a count formula and minimizing manual work?

[VahidDM]

Hi @NJ13 

Try to unpivote your table in Power Query, then you can use Attribute or Value columns to find the count of each categories with some filters.

https://docs.microsoft.com/en-us/power-query/unpivot-column

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[NJ13]

@Greg_Deckler i have two tables one with the below texts and the other i shared above

i want to count for each of these texts words in the other table for each column and then will sum them up after getting the counts. do you think that is possible ? if so can you please help me out with a formula and/or how to do it?

thank you 

[NJ13]

 i have two tables one with the below texts and the other i shared above

i want to count for each of these texts words in the other table for each column and then will sum them up after getting the counts. do you think that is possible ? if so can you please help me out with a formula and/or how to do it?

[Greg_Deckler · Accepted solution]

@NJ13 Right, so, again, the best solution would be to unpivot your columns in your first table, form a relationship between the two tables and then it is simply a COUNTROWS. Otherwise, you will need to use MC Aggregations like this:

Count MC Aggregations Column =
  VAR __Service = 'Table2'[Service]
  VAR __Column1 = SELECTCOLUMNS('Table1',"__Column",[Column1]
  VAR __Column2 = SELECTCOLUMNS('Table1',"__Column",[Column2]
  VAR __Column3 = SELECTCOLUMNS('Table1',"__Column",[Column3]
  VAR __Column4 = SELECTCOLUMNS('Table1',"__Column",[Column4]
  VAR __Table = UNION(__Column1, __Column2, __Column3, __Column4)
RETURN
  COUNTROWS(FILTER(__Table, [__Column] = __Service))
  

[Greg_Deckler]

@NJ13 So typically you would unpivot the columns and then it is simple, you can use DISTINCTCOUNT for example. Otherwise, you could use MC Aggregations with a COUNTROWS(DISTINCT(UNION(...)))

Multi-Column Aggregations (MC Aggregations) - Microsoft Power BI Community