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:

Mayan_0-1596496869123.png

And he needed to make a table showing the country with its respective frequency percentage considering the 4 columns.
I'm very grateful for your help!

  • 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

3 Replies

  • 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

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , The best way is to unpivot the column.

    https://radacad.com/pivot-and-unpivot-with-power-bi
    Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

     

    The second way is to join all four columns with a country table, one active and three inactive joins will be there. Then create four-count measure using userelation and sum that up

    https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

  • Anonymous's avatar
    Anonymous
    Not applicable
    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