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
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