Create a line chart displaying the number of customers by number of visits made

[Anonymous]

Hello everyone,

I would like help developing a visual for my power Bi report.

The data imported into my file comes exclusively from SalesForce thanks to a connection via an object.

I have different tables:

- An "Account" table which displays all of our company's customers. Below, a detail of the columns of this table:

Id, Name, OwnerId, Customer_Type__c,

- An Event table which displays all the events entered by our salespeople on Salesforce. As soon as a salesperson enters an event on his calendar, then it is recorded in this table. What interests us here are the visits made by salespeople to their customers. I have a calculated column that returns 1 if the event in question is a visit and returns 0 if it is not. Below is a detail of the columns of this table:

Id, AccountId, OwnerId, Date, Visits. (The “Visits” column being the one that returns 1 or 0)

- A date table which includes all the dates of the year, with columns such as "Month", "Week", etc., allowing the data to be filtered in my report view using segments.

FYI, the 3 tables mentioned above are linked by relationships:

- The Event table is linked to the Account table thanks to the "AccountId" (Event table) and "Id (Account table") columns.

- The Event table is linked to the Date table thanks to the "Date" columns present in both tables.

Here is my need:

I would like to create a line chart that will display: the number of customers based on the number of visits made over a certain period. Basically, the graph should show me how many customers have been seen 0 times, how many have been seen 1 time, how many have been seen 2 times etc. The data displayed in this chart must be related to the selected date period.

Example: a client was seen once in January, once in February and once in March. When I select the January period in my segment, the graph should show me that this customer has been seen once. On the other hand, if I select a period covering January to March, the graph should show me that the customer has been seen 3 times.

Knowing that the Event table does not allow you to know which customers have not been seen, you must use the Account table in order to retrieve the customers who have not been seen by the salespeople. Indeed, the Event table shows the events carried out, if a customer has not been seen, then there is no event concerning him in the Event table.

My issue is the following :

I have already explored various solutions including measures. However, it is impossible for me to enter a measure in the “X Axis” field of a curve graph. I also tried using calculated columns but the results obtained are not linked to the selected date period.

I have been looking for a solution to this problem for a long time, but I have no way to solve it, especially since I am not an expert in this software. So I am open to any solution you could offer me.

Thank you very much for your help.

[Anonymous]

Hi @Anonymous 

Can you provide detailed sample pbix file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.

Best Regards,

Jayleny

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

[Anonymous]

Hello johnbasha33

Thank you very much for your response and the time you gave me.

Unfortunately, the proposed solution does not meet my needs. This is not a question of knowing the number of customers who have been visited or not visited. We seek to know the number of customers based on the number of visits made. Basically, I would like to know how many customers were seen once, how many were seen twice, three times, etc.

So, I would have to have:

in X Axis: number of visits (0, 1, 2, 3, 4, etc.)

On the Y axis: the number of customers

The date column should not be taken into account in my chart. I already have date segments allowing me to filter my visuals. I am attaching a capture of a line chart made in Excel for more detail. Thank you again for your answer.

[johnbasha33]

@Anonymous 

To achieve your desired visualization in Power BI, you can follow these steps:

1. **Create a Measure for Customer Visits**:
First, create a measure in your Event table that calculates the number of visits per customer. This measure should sum the Visits column in the Event table.

```DAX
Customer Visits = SUM('Event'[Visits])
```

2. **Create a Measure for Total Customers**:
Next, create a measure in your Account table that counts the number of customers.

```DAX
Total Customers = COUNTROWS('Account')
```

3. **Create a Measure for Unique Customers with Visits**:
Create a measure to count the number of unique customers who have visits. This measure should count the distinct AccountIds in the Event table where visits are greater than zero.

```DAX
Unique Customers with Visits =
CALCULATE(
DISTINCTCOUNT('Event'[AccountId]),
FILTER('Event', 'Event'[Visits] > 0)
)
```

4. **Create a Measure for Customers with No Visits**:
Create a measure to count the number of customers who have not had any visits. This measure should subtract the number of unique customers with visits from the total number of customers.

```DAX
Customers with No Visits = [Total Customers] - [Unique Customers with Visits]
```

5. **Create a Line Chart**:
Create a line chart visualization with the following settings:
- X Axis: Use your date column from the Date table.
- Values: Use the measures created above (`Unique Customers with Visits` and `Customers with No Visits`).
- Legend: Use the customer visit status (`With Visits`, `No Visits`).

By following these steps, you'll be able to create a line chart that displays the number of customers based on the number of visits made over a certain period. The chart will dynamically adjust based on the selected date range, showing the count of customers with visits and without visits.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!