Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Counting values in different columns

Hello! I need your help. I require an analysis of the frequency of different columns. The basis I have is like this: And he needed to make a table showing the country with its respective freque...
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

    According to my understanding ,you want to calculate the frequency of the values under different columns,right?
    I did it in two ways using your sample.
     
    1.The easiest way is to use "unpivot column" in Query Editor.
     

    My 100% stacked column chart looks like this:

     

    2.Using the following formula:

     

     //Filter out the different values in the four columns as a table(column)

     

    All values table =
DISTINCT (
    UNION (
        DISTINCT ( 'Counting values'[Pais1] ),
        DISTINCT ( 'Counting values'[Pais2] ),
        DISTINCT ( 'Counting values'[Pais3] ),
        DISTINCT ( 'Counting values'[Pais4] )
    )
)

     


    //Count of different values in each column

     

    COUNT1 =
CALCULATE (
    COUNT ( 'Counting values'[Pais1] ),
    FILTER (
        'Counting values',
        'Counting values'[Pais1] = SELECTEDVALUE ( 'All values table'[distinct values] )
    )
)

     

     

    COUNT2 =
CALCULATE (
    COUNT ( 'Counting values'[Pais2] ),
    FILTER (
        'Counting values',
        'Counting values'[Pais2] = SELECTEDVALUE ( 'All values table'[distinct values] )
    )
)

     

     

    COUNT3 =
CALCULATE (
    COUNT ( 'Counting values'[Pais3] ),
    FILTER (
        'Counting values',
        'Counting values'[Pais3] = SELECTEDVALUE ( 'All values table'[distinct values] )
    )
)

     

     

    COUNT4 =
CALCULATE (
    COUNT ( 'Counting values'[Pais4] ),
    FILTER (
        'Counting values',
        'Counting values'[Pais4] = SELECTEDVALUE ( 'All values table'[distinct values] )
    )
)

     

     

    My 100% stacked column chart looks like this:

    Are these results what you want? If you have any questions, please upload some data samples and expected output.
    Please do mask sensitive data before uploading.

    Best Regards,
    Eyelyn Qin
mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

The easiest way to do this is to unpivot these 4 columns in the query editor.  You will then have a table with two columns (Attribute and Value, unless you name them otherwise).  You can then make a Table visual with the Value column, with a simple measure to count the number of rows.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat