Forum Discussion

cgoetze's avatar
cgoetze
Frequent Visitor
3 years ago
Solved

Count/Distinctcount in Columns as a New Table/Visual

Hi,

I need help to Distinctcount and group the result as a simple table.

 

Table Structure:

Table1 
Name - ID
John - X234
John - W567
John - 8910
Maria - W567
Maria - X234
Leo - 2345
Leo - 6789
Leo - X234
Richard - X234
Richard - W567
Richard - 9012
Richard - 6789
Alex - X234
Alex - W567
Alex - 8910
Tiffany - W567
Tiffany - X234
Marc - 2345
Marc - 6789
Marc - X234
Rafael - X234
Rafael - W567
Rafael - 9012
Rafael - 6789

 

Result as Visual (table)

Table A
Count ID - Count Names
2 - 2
3 - 4
4 - 2

 

In other table a range of names in every ID

 

Table B
Range count Names - Count ID
1-3 - 3
4-6 - 2
>7 - 1

 

Thanks.

Need Help
Tutorial Requests

5 Replies

  • VijayP's avatar
    VijayP
    Icon for Community Champion rankCommunity Champion
    3 years ago

    cgoetze 

    This question is bit vague. 

    when you create distinct count of both columns answer will not be more than 1 row. Try  to add some data with business perspective and also add why you want to see that!

  • cgoetze's avatar
    cgoetze
    Frequent Visitor
    3 years ago

    Hi,Is a Statistics Analysis, not so related with business. But I tried my best.

    >>>

    Data 

  • cgoetze's avatar
    cgoetze
    Frequent Visitor
    3 years ago
    Table1
    Name - ID
    John - X234
    John - W567
    John - 8910
    Maria - W567
    Maria - X234
    Leo - 2345
    Leo - 6789
    Leo - X234
    Richard - X234
    Richard - W567
    Richard - 9012
    Richard - 6789
    Alex - X234
    Alex - W567
    Alex - 8910
    Tiffany - W567
    Tiffany - X234
    Marc - 2345
    Marc - 6789
    Marc - X234
    Rafael - X234
    Rafael - W567
    Rafael - 9012
    Rafael - 6789

    Result:
    TableA
    Count ID - Count Name (Distinct)
    1 - 0
    2 - 2
    3 - 4
    4 - 2
    5 - 0

    In the table above we have 0 names with 1 ID, 2 Names with 2 IDs....

    *The numbers 1 and 5 could be omitted.

    In excel i could do a dinamic table with Name in line and count ID in value, and after that a table that "count if" using the dinamic table as source.

    Thanks.

     

    NameID
    John X234
    John W567
    John 8910
    Maria W567
    Maria X234
    Leo 2345
    Leo 6789
    Leo X234
    Richard X234
    Richard W567
    Richard 9012
    Richard 6789
    Alex X234
    Alex W567
    Alex 8910
    Tiffany W567
    Tiffany X234
    Marc 2345
    Marc 6789
    Marc X234
    Rafael X234
    Rafael W567
    Rafael 9012
    Rafael 6789

     

    For TableA

     

     

    For TableB

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi cgoetze ,

    I have created a simple sample, please refer to my pbix file to see if it helps you.

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Polly

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.