Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
fiorela
Helper I
Helper I

how to summarize data, count category variable

Hi all,

I have this type of table in my powerbi Desktop:

 

Type1                 Type2                 Type3               type4

intermediate       Intermediate      Advanced        Awareness 

Novice                Intermediate      Novice              Awareness

Advanced            Novice               Novice              Intermediate

Awareness           Novice               Novice              Advanced

 

and i would like to convert it into:

 

                   Novice     Intermediate        Advanced      Awareness

Type1               1               1                          1                     1

Type2              2                2                           0                     0

Type3

Type4

 

 

 

Thank you!

2 ACCEPTED SOLUTIONS
parry2k
Super User
Super User

@fiorela solution attached, look at table(2), lot of heavy lifting is done in power query

 

- added index column

- unpivoted the table

and then everything was very easy



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

In the Query Editor, add an Index column.  Click on the Index heading and select "Unpivot Other olumns".  Build a matrix visual and drag Attribute to the row labels.  Drag the 3rd column to the column labels.  Write this measure

=COUNTROWS(Data)

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

In the Query Editor, add an Index column.  Click on the Index heading and select "Unpivot Other olumns".  Build a matrix visual and drag Attribute to the row labels.  Drag the 3rd column to the column labels.  Write this measure

=COUNTROWS(Data)

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

appreciate your help. many thanks !

parry2k
Super User
Super User

@fiorela solution attached, look at table(2), lot of heavy lifting is done in power query

 

- added index column

- unpivoted the table

and then everything was very easy



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

 many thanks!! it works

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.