The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 their assigned companies
Table 2: Name of the Sales Person and the companies the have contacted and the date. There are multiple types of contacts in the table, i.e. e-mail, in person, phone call etc.
My issues is when I filter to show just the in person acticities, I lose the names of the companies where there have been no activities.
Hi @LMorman,
Would you please share sample data of the above two tables and post the expected result? Besides, we need to know that how you filter the table visuals.
Thanks,
Lyida Zhang
Not sure if this will work...
We don't have access to this sharepoint location
How else can I share data?
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.
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.
Hi @LMorman,
The above table is your expected result, right? Could you please also post sample data of your source tables?
Thanks,
Lydia Zhang
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
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
what is the best way to share the data. I am having issues with copy and paste from Excel into the relply box
Save workbook to a cloud storage (i.e. OneDrive) and share the link
When you filter, the data that doesn't have corresponding values for your filter/slicer will not be shown. That is the point of a filter. Maybe I am not understanding your question?
I have a list of Companies assigned to a sales person and a list of activities the sales pro has completed with their assigned companies. They track activity types such as face to face meetings, e-mails, phone calls etc.
I want to show the sales pros full list of clients regardless of whether or not they have had an activity and only show the face to face activities ( by activity date)
Yes, yes you would.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
88 | |
71 | |
65 |
User | Count |
---|---|
241 | |
124 | |
120 | |
81 | |
79 |