Forum Discussion

DashboardNewbie's avatar
DashboardNewbie
Regular Visitor
10 years ago
Solved

Counting Types of Records

This may seem like the most basic of problems, but I have an xls file with 100,000+ customer rows.  There are 300+ columns against each of the rows that classify the customer in two ways 

 

Descriptive Columns:  such as Age  / Gender and as you can tell, these have pre-set values filled in such as 

 

Under Age, 18-24 or 25 - 34 and so on..

Under Gender, Values are M or F

 

Binary Columnes:  These are questions answered in a yes/no fashion, such as 

 

Interested in Movies: Y/N

Has Cable at Home: Y/N

 

I am trying to create an audience persona with this data, that helps me create a report that takes each column name and shows me the count of each of the distinct values in the column.  So for example, for "Gender" Count of Male / Female, and for "Interested in Movies" Count of Y

 

How do I transform my table - I know this is a lengthy problem but I am an absolute beginner and am trying to learn the most basic transformation to generate some reports

 

Thank you all in advance for your help for pointing me in the right direction 

  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    DashboardNewbie

     

    If I understand your requirement correctly, for a table as below,

     

    You will need two calculated tables, one as

     

     

    Table = 
SUMMARIZE (
    Table13,
    "CNT Female", COUNTAX (
        FILTER ( Table13, Table13[Under Gender] = "F" ),
        Table13[Under Gender]
    ),
    "CNT Interested in Movie", COUNTAX (
        FILTER ( Table13, Table13[Interested in Movies] = "Y" ),
        Table13[Interested in Movies]
    ),
    "CNT Has Cable at home", COUNTAX (
        FILTER ( Table13, Table13[Has Cable at Home] = "Y" ),
        Table13[Has Cable at Home]
    ),
    "CNT 18-24", COUNTAX ( FILTER ( Table13, Table13[Under Age] = "18-24" ), Table13[Under Age] ),
    "CNT 25-34", COUNTAX ( FILTER ( Table13, Table13[Under Age] = "25-34" ), Table13[Under Age] ),
    "CNT 35-44", COUNTAX ( FILTER ( Table13, Table13[Under Age] = "35-44" ), Table13[Under Age] )
)

     

    The other as

     

    Table2 = 
UNION (
    SELECTCOLUMNS ( 'Table', "category", "cnt Female", "cnt", 'Table'[CNT Female] ),
    SELECTCOLUMNS (
        'Table',
        "category", "cnt Has Cable at home",
        "cnt", 'Table'[CNT Has Cable at home]
    ),
    SELECTCOLUMNS (
        'Table',
        "category", "cnt Intetrested in Movie",
        "cnt", 'Table'[CNT Interested in Movie]
    ),
    SELECTCOLUMNS ( 'Table', "category", "cnt 18-24", "cnt", 'Table'[CNT 18-24] ),
    SELECTCOLUMNS ( 'Table', "category", "cnt 25-34", "cnt", 'Table'[CNT 25-34] ),
    SELECTCOLUMNS ( 'Table', "category", "cnt 35-44", "cnt", 'Table'[CNT 35-44] )
)

6 Replies

  • BhaveshPatel's avatar
    BhaveshPatel
    Icon for Super User rankSuper User
    10 years ago

     

    Hi there

     

    Connect to the your excel file and import data into powerBI making sure that all the data types are correctly interpreted by the PowerBI Engine. 

     

    You can drag and drop the particular columns into the values sections againest columns and rows. 

    It can also give the value of distinct count if you select the drop down of the field/s in the values section. 

     

    Use table Visual for this purpose. Hope this will help you understand.

     

    or use 

    COUNT or DISTINCTCOUNT  DAX Formula by selecting one of the columns from your table and right clicking and choosing new measure option.

     

     

     

     

     

    • DashboardNewbie's avatar
      DashboardNewbie
      Regular Visitor
      10 years ago

      Thanks Bhavesh for the help, and I could see the totals against each category in the drop down as you suggested.. However I'm not sure how to get the data in the table visual against each category... 

       

      Here's an example screenshot of my data

       

       

       

      And, this is where I would like the count of each of the age categories to appear

       

       

       

       

      • Eric_Zhang's avatar
        Eric_Zhang
        Icon for Microsoft Employee rankMicrosoft Employee
        10 years ago

        DashboardNewbie

         

        If I understand your requirement correctly, for a table as below,

         

        You will need two calculated tables, one as

         

         

        Table = 
SUMMARIZE (
    Table13,
    "CNT Female", COUNTAX (
        FILTER ( Table13, Table13[Under Gender] = "F" ),
        Table13[Under Gender]
    ),
    "CNT Interested in Movie", COUNTAX (
        FILTER ( Table13, Table13[Interested in Movies] = "Y" ),
        Table13[Interested in Movies]
    ),
    "CNT Has Cable at home", COUNTAX (
        FILTER ( Table13, Table13[Has Cable at Home] = "Y" ),
        Table13[Has Cable at Home]
    ),
    "CNT 18-24", COUNTAX ( FILTER ( Table13, Table13[Under Age] = "18-24" ), Table13[Under Age] ),
    "CNT 25-34", COUNTAX ( FILTER ( Table13, Table13[Under Age] = "25-34" ), Table13[Under Age] ),
    "CNT 35-44", COUNTAX ( FILTER ( Table13, Table13[Under Age] = "35-44" ), Table13[Under Age] )
)

         

        The other as

         

        Table2 = 
UNION (
    SELECTCOLUMNS ( 'Table', "category", "cnt Female", "cnt", 'Table'[CNT Female] ),
    SELECTCOLUMNS (
        'Table',
        "category", "cnt Has Cable at home",
        "cnt", 'Table'[CNT Has Cable at home]
    ),
    SELECTCOLUMNS (
        'Table',
        "category", "cnt Intetrested in Movie",
        "cnt", 'Table'[CNT Interested in Movie]
    ),
    SELECTCOLUMNS ( 'Table', "category", "cnt 18-24", "cnt", 'Table'[CNT 18-24] ),
    SELECTCOLUMNS ( 'Table', "category", "cnt 25-34", "cnt", 'Table'[CNT 25-34] ),
    SELECTCOLUMNS ( 'Table', "category", "cnt 35-44", "cnt", 'Table'[CNT 35-44] )
)