Forum Discussion

fiorela's avatar
fiorela
Helper I
6 years ago
Solved

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  N...
Need Help
Show and Tell
  • parry2k's avatar
    parry2k
    6 years ago

    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

    table.pbix39 KB
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    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.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

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.

fiorela's avatar
fiorela
Helper I
6 years ago

appreciate your help. many thanks !