How can we count from the single column have diffrrent names within same cells by using GroupBy?

[parry2k · Accepted solution]

@nitinbhai you should unpivot your data 

- transform data
- select Id and status column in your table
- right-click, unpivot other columns it will add two columns, attribute, and value, rename these as per your requirement
- close and apply

To visualize,
- matrix visual:
- add attribute on rows,
- add status on columns
- add id on values section with aggregation as count

this is the best approach and you will able to slice/dice the data as per your business requirement and it is scalable solution 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[v-lili6-msft · Accepted solution]

hi  @nitinbhai 

First, for your case, you'd better unpivot your data as parry2k solution.

https://radacad.com/pivot-and-unpivot-with-power-bi

And that will change your data structure, otherwise, you need to create measure for each color as below:

Red = COUNTROWS(FILTER('Table','Table'[Color1]="Red"))+COUNTROWS(FILTER('Table','Table'[Color2]="Red"))+COUNTROWS(FILTER('Table','Table'[Color3]="Red"))+COUNTROWS(FILTER('Table','Table'[Color50]="Red"))

White = COUNTROWS(FILTER('Table','Table'[Color1]="White"))+COUNTROWS(FILTER('Table','Table'[Color2]="White"))+COUNTROWS(FILTER('Table','Table'[Color3]="White"))+COUNTROWS(FILTER('Table','Table'[Color50]="White"))

Blue = COUNTROWS(FILTER('Table','Table'[Color1]="Blue"))+COUNTROWS(FILTER('Table','Table'[Color2]="Blue"))+COUNTROWS(FILTER('Table','Table'[Color3]="Blue"))+COUNTROWS(FILTER('Table','Table'[Color50]="Blue"))

Black = COUNTROWS(FILTER('Table','Table'[Color1]="Black"))+COUNTROWS(FILTER('Table','Table'[Color2]="Black"))+COUNTROWS(FILTER('Table','Table'[Color3]="Black"))+COUNTROWS(FILTER('Table','Table'[Color50]="Black"))

Then create a visual as below:

Regards,

Lin

[nitinbhai · Accepted solution]

Also, one important thing once we do pivot for the existing data in powerbi, it will works when add new data monthly from the source, unpiviot automatically transpose data and update monthly automatically when we refresh data...is that correct?

It is really appreciate your support so far.

[az38]

Hi @nitinbhai 

please, provide more details about your requirements: data sample and desired output

[nitinbhai]

ID	Color1	Color2	Color3	Color50	Status
1	Red	Blue	Black	White	Low
2		Blue	Black	White	High
3			Black	White	Medium
4	Red		Black		High

I have data like above table, and needs to count based on status vs. color... desired output will be like below

	Low	Medium	High
Red	1		1
Blue	1		1
White	1	1	1
Black	1	1	2

[v-lili6-msft · Accepted solution]

hi  @nitinbhai 

First, for your case, you'd better unpivot your data as parry2k solution.

https://radacad.com/pivot-and-unpivot-with-power-bi

And that will change your data structure, otherwise, you need to create measure for each color as below:

Red = COUNTROWS(FILTER('Table','Table'[Color1]="Red"))+COUNTROWS(FILTER('Table','Table'[Color2]="Red"))+COUNTROWS(FILTER('Table','Table'[Color3]="Red"))+COUNTROWS(FILTER('Table','Table'[Color50]="Red"))

White = COUNTROWS(FILTER('Table','Table'[Color1]="White"))+COUNTROWS(FILTER('Table','Table'[Color2]="White"))+COUNTROWS(FILTER('Table','Table'[Color3]="White"))+COUNTROWS(FILTER('Table','Table'[Color50]="White"))

Blue = COUNTROWS(FILTER('Table','Table'[Color1]="Blue"))+COUNTROWS(FILTER('Table','Table'[Color2]="Blue"))+COUNTROWS(FILTER('Table','Table'[Color3]="Blue"))+COUNTROWS(FILTER('Table','Table'[Color50]="Blue"))

Black = COUNTROWS(FILTER('Table','Table'[Color1]="Black"))+COUNTROWS(FILTER('Table','Table'[Color2]="Black"))+COUNTROWS(FILTER('Table','Table'[Color3]="Black"))+COUNTROWS(FILTER('Table','Table'[Color50]="Black"))

Then create a visual as below:

Regards,

Lin

[nitinbhai]

I have thousands of raws of data and needed to be count multiple response from col1 to col 50 vs. status. It is really appreciated your help if any we can do in powerbi without stacking data.

[parry2k · Accepted solution]

@nitinbhai you should unpivot your data 

- transform data
- select Id and status column in your table
- right-click, unpivot other columns it will add two columns, attribute, and value, rename these as per your requirement
- close and apply

To visualize,
- matrix visual:
- add attribute on rows,
- add status on columns
- add id on values section with aggregation as count

this is the best approach and you will able to slice/dice the data as per your business requirement and it is scalable solution 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[nitinbhai]

As you mentioned select ID and status columns then pivot, then do i need to select the multiple column for the color:1-50 columns as well when we select unpivot.

How about if we 3 different scenario like

1. multiple column of color

2. multiple columnd of author

3. multiple column of areas

if we need to transfrom data by selecting uniqueid and status vs. all 3 different scenario OR can we indivual unpivot  by selecting status and id vs. scenario1

selecting status and id vs. scenario2

selecting status and id vs. scenario3

[nitinbhai]

Also using below query infidivually

Red = COUNTROWS(FILTER('Table','Table'[Color1]="Red"))+COUNTROWS(FILTER('Table','Table'[Color2]="Red"))+COUNTROWS(FILTER('Table','Table'[Color3]="Red"))+COUNTROWS(FILTER('Table','Table'[Color50]="Red"))

White = COUNTROWS(FILTER('Table','Table'[Color1]="White"))+COUNTROWS(FILTER('Table','Table'[Color2]="White"))+COUNTROWS(FILTER('Table','Table'[Color3]="White"))+COUNTROWS(FILTER('Table','Table'[Color50]="White"))

Blue = COUNTROWS(FILTER('Table','Table'[Color1]="Blue"))+COUNTROWS(FILTER('Table','Table'[Color2]="Blue"))+COUNTROWS(FILTER('Table','Table'[Color3]="Blue"))+COUNTROWS(FILTER('Table','Table'[Color50]="Blue"))

Black = COUNTROWS(FILTER('Table','Table'[Color1]="Black"))+COUNTROWS(FILTER('Table','Table'[Color2]="Black"))+COUNTROWS(FILTER('Table','Table'[Color3]="Black"))+COUNTROWS(FILTER('Table','Table'[Color50]="Black"))

it's a lot when we have 100 multiple columns, so i need to write 100 measure for 100 multiple response to combine.

[nitinbhai · Accepted solution]

Also, one important thing once we do pivot for the existing data in powerbi, it will works when add new data monthly from the source, unpiviot automatically transpose data and update monthly automatically when we refresh data...is that correct?

It is really appreciate your support so far.

[v-lili6-msft · Accepted solution]

hi  @nitinbhai 

You just need to unpivot color columns in power query.

yes, if there are 100 columns, you need to write 100 measure for 100 multiple response to combine, so it is better unpivot all the color columns into one column to calculate them together.

Yes, if the data structure are same, it will automatically transpose data.

Regards,

Lin

[parry2k]

@nitinbhai great your issue is resolved but it is weird that you marked your own reply as a solution. Maybe that's what it is but it will more useful if you mark accept the actual post as a solution that helps to resolve your problem so that other can get benefit from it. Just a thought.

[parry2k]

@nitinbhai add a new measure

Measure = COUNTROWS ( Table )

drop a table visual, add your column and mesure (above) in the visual and you will have it.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡