Counting values in different columns

[Mayan]

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 frequency percentage considering the 4 columns.
I'm very grateful for your help!

[v-eqin-msft · Accepted solution]

Hi @Mayan ,

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

[amitchandak]

@Mayan , 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...

[mahoneypat]

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