cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
keelverner Frequent Visitor
Frequent Visitor

Counting text records from three columns and presenting sum values in a visualisation

Hi, 

 

I have three columns in a table that are containing employee names.

EXAMPLE DATA 
CallsEnquiriesMeetings
JohanRachel Mark
JohanRachel Mark
JohanMarkMark
JohanJohanRachel
JohanRachel Johan
MarkMark Johan
RachelMarkRachel
MarkMarkRachel

 

Is it possible to create a visualisation in Power Bi which would list all employees and count their appearance in those three columns?  The real dataset contains 50+ names and 10k+ rows.

DESIRED OUTCOME  
EmployeeCallsEnquiriesMeetings
Rachel133
Mark243
Johan512

 

It is easy to so with one column but I could not figure out how to do it with more columns. Could anybody please point me to the right direction? 

 

All the best,
Verner

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Counting text records from three columns and presenting sum values in a visualisation

Hi @keelverner 

One of the options is to unpivot the columns.

Another is option is the following, where Table1 is the table you show

1. Create  a one-column calculated table with the names of the employees:

Employees = 
SELECTCOLUMNS (
    DISTINCT (
        UNION (
            ALL ( Table1[Calls] );
            ALL ( Table1[Enquiries] );
            ALL ( Table1[Meetings] )
        )
    );
    "Name"; [Calls]
)

 

 

2. Create these 3 measures:

 

MCalls = 
CALCULATE (
    COUNT ( Table1[Calls] );
    TREATAS ( DISTINCT ( Employees[Name] ); Table1[Calls] )
)
MEnquiries = 
CALCULATE (
    COUNT ( Table1[Enquiries] );
    TREATAS ( DISTINCT ( Employees[Name] ); Table1[Enquiries] )
)
MMeetings = 
CALCULATE (
    COUNT ( Table1[Meetings] );
    TREATAS ( DISTINCT ( Employees[Name] ); Table1[Meetings] )
)

3. Place Employees[Name] in the rows of a matrix visual and the three measures in values of the matrix

 

 

You can see it working here

2 REPLIES 2
Highlighted
Super User
Super User

Re: Counting text records from three columns and presenting sum values in a visualisation

Hi @keelverner 

One of the options is to unpivot the columns.

Another is option is the following, where Table1 is the table you show

1. Create  a one-column calculated table with the names of the employees:

Employees = 
SELECTCOLUMNS (
    DISTINCT (
        UNION (
            ALL ( Table1[Calls] );
            ALL ( Table1[Enquiries] );
            ALL ( Table1[Meetings] )
        )
    );
    "Name"; [Calls]
)

 

 

2. Create these 3 measures:

 

MCalls = 
CALCULATE (
    COUNT ( Table1[Calls] );
    TREATAS ( DISTINCT ( Employees[Name] ); Table1[Calls] )
)
MEnquiries = 
CALCULATE (
    COUNT ( Table1[Enquiries] );
    TREATAS ( DISTINCT ( Employees[Name] ); Table1[Enquiries] )
)
MMeetings = 
CALCULATE (
    COUNT ( Table1[Meetings] );
    TREATAS ( DISTINCT ( Employees[Name] ); Table1[Meetings] )
)

3. Place Employees[Name] in the rows of a matrix visual and the three measures in values of the matrix

 

 

You can see it working here

keelverner Frequent Visitor
Frequent Visitor

Re: Counting text records from three columns and presenting sum values in a visualisation

Thank you @AlB for the straightforward and elegant solution. Much appriciated. 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 350 members 3,375 guests
Please welcome our newest community members: