Forum Discussion

LMorman's avatar
LMorman
Helper I
9 years ago

Displaying items with no data

  I am trying to create a matrix to display a Sales Person portfolio of clients and who they have met with in person, when and who they have not. Table 1:  includes the name of the Sales Person and...
dkay84_PowerBI's avatar
dkay84_PowerBI
Microsoft Employee
9 years ago

Just give us a small sample in some format like such:

 

Column1  Column2  Column3

data1        data2       data3

data4        data5       data6

 

Or use a file storage service like Box, OneDrive, GoogleDocs etc.

LMorman's avatar
LMorman
Helper I
9 years ago

Sales Pro Name              Company Name              November            December           January            Total

Mary Smith                         Company 1                          1                                                     2                   3

                                            Company 2                                                         2                      2                   4

                                            Company 3                                                                                                      0

 

The counts represent # of Face to Face calls by the Sales Pro during the month. 

 

The first table (Entities Table) I use contains the name of the Sales Pro and all of their assigned Entities

The second table Activities Table) contains the Entities where there were activities, the dates of the activities and the Sales Pro name.

The Activities Table contains additional activities, such as e-mail, phone call etc. 

I want to be able to show a Sales Pro's full portfolio and who they have had a face to face call with and who they haven't.

I also need to count how many Companies Mary Smith has not had a Face to Face call with.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi LMorman,


    The above table is your expected result, right? Could you please also post sample data of your source tables?

    Thanks,
    Lydia Zhang

    • LMorman's avatar
      LMorman
      Helper I
      9 years ago

      Entity Table

       

      Sales Pro Name   Entity Name       

      Mary Smith           Company 1

      Mary Smith           Company 2

      Mary Smith          Company 3

       

       

      Activity Table

       

      Entity Name      Date of Activity      Activity Type

      Company 1               11/1/2015            Face to Face

      Company 1                01/02/2016         Face to Face

      Company 1               01/10/2016          Face to Face

      Company 1               05/01/2016           e-mail

      Company 2              12/01/2015           Face to Face

      Company 2              12/10/2015           Face to Face

      Company 2              01/02/2016           Face to Face

      Company 2             01/10/2016            Face to Face

      Company 2               05/10/2016          Phone Call 

       

       

      *Company 3 does not exist in Activity table

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi LMorman,

        In your scenario, please create a new column using the following formula, then create a Matrix visual as shown in the first screenshot. Please note that in order to show company 3, you need to right-click Entity field and select “Show items with no data” as shown in the second screenshot. For more details, you can review this attached PBIX file.

        Column = CALCULATE(COUNTA(Activity[Activity Type]),Activity[Activity Type]="Face to Face")



        Thanks,
        Lydia Zhang