Customer Visit Coverage

[Nieto1976]

Hello everyone,

Please could you help me with this topic? I would like to create a meassure in Power BI to know the customer visit coverage. I have a customer segmentation (A, B and C), Customers A must be visited every month, customers B must be visited one time every two months and customers C one time every quarter. In a Fat table I have the visits done by sales reps to the customers. Do you know how I can calculate this messure?

[speedramps]

We want to help you but this question looks like it was written in a rush.

You will get quicker answers if you take time to write clear decsriptions. 😁😁

Do you mean that you customer table as a ABC segmentation field  (A should be visited every month,  B should visited every two months and C shoud be visted every quarter.)

Sound like you have a vistits table with date, salesperson and customers.

And perhaps you need a report showing targets that have been missed and achieved.

Showing history for all customers on same visual will be tricky because the period ranges overlap.

You cant compare apples with oranges and bananas !

So I suggest you have 3 report pages  (Monthly, Bi Monthly, Quarterly) with a buttons 

Create a Calandar table with

date, start of month, start of bi month, start of quarter

Create 1:m relationship from calandar[date] to vists[date]

create page matrix1

filter = A

row = customer

column = calendar[startofmonth]

value = countrow(visits)

create page matrix2

filter = B

row = customer

column = calendar[startofbimonth]

value = countrow(visits)

create page matrix2

filter = C

row = customer

column = calendar[startofquarter]

value = countrow(visits)

Hide page 2 and 3

Add ABC navigation buttons to page 1,2,3 on each page.

So when use select button C they will see quarters.

The user will think all 3 reports are the same report.

Only you and I will know how it really works.

It is a pretty cool solution 😎

Please do click the thumbs up and accept as solution button. Thank you !

[Nieto1976]

Hello and thanks for help me. You made a good description about the data that we have:

1. Customer Master data (Customer ID, Name, Classfication)
2. Fat visit table (Sales rep, customer ID and date of the visit)

I made this table to show you what I want to create in Power BI. I know the number of visits that the sales reps have to do based on the segmentation of customer, to no compare apple with oranges or bananas I apply a correction in Customers B and C, for customers B multiply number of customer visited for 0,5 and customer C multiply for 0,33. Then I can divide for number of customer visited in this month to calculate the coverage.

[speedramps · Accepted solution]

Hi again Nieto1976

As previously mentioned, you are comparing apples with oranges and bananas !

Monthly, with bi-monthly with quarterly (1,2 and 3s)  

Bi-monthly has 2 months and quarters has 3 months.

So you cant easily do a like on like comparison by month.

Consider the method I suggested with a ABC buttons,

that shows monthly, with bi-monthly with quarterly reports.

But gives the users the illusion that it is one nice interactive report.

It is quick to build and will work.

Please do click the thumbs up and accept as solution button. Thank you !

[amitchandak]

@Nieto1976 , Is related to RFM?

https://ploiitubsamon.medium.com/rfm-analysis-for-customer-segmentation-with-power-bi-5d2f5bd62038

https://powerbiexperience.com/en/blog/using-dax-for-customers-segmentation-by-rfm-recency-frequency-...