Count Customer Visited & follow/Unfollow plan Order & No Order

[ADSL]

Hi BI Community Team,

I am a new Power BI User and need your support of report that want to transfer from excel to power BI.

I have 02 tables --- customer route plan visit & daily customer visited of sales rep.

>> I am creating the new column and merging the column name: sales rep code + customer code + call date in these 02 tables.

>> And using vlookup to mapping and find --- Visited & Un-Visit ; Plan & Unplan. Column name "ORD_IND" refer to sales order & "CALL_IND" refer unique customer count if visited more than one time.

I am using the pivot of customer route plan table to find: Customer Call Plan, Customer Visited, Customer Un-Visit.

I am using the pivot of daily customer visited to find: Total customer call order, customer order follow plan , customer unplan order.

May I know your suggestion if we move into power BI calculation? 

Here is the pbix file - https://drive.google.com/drive/folders/1_CRwD6g67byE1ayhil5sKIi0MkqlJ9Xk?usp=sharing 

Thanks and Regards,

[Ashish_Mathur]

Hi,

Your question is not clear even after reading your response to MFelix.  Show the formulas/steps/Pivot Table in an MS Excel file.  Once i understand the logic in that file, I will try to translate those formulas into the DAX formula language.

[ADSL]

Hi @Ashish_Mathur,

Thank you for your feedback.

Actually we have 02 tables:

1. Customer Route Plan => refer to Call Plan that sales rep follow this plan to visit customer everyday.

2. Daily Customer Visited => refer to Call Visited that sales rep had visited already.

Based on these tables, we want to find the report listing below of sales rep.

• Call Plan
• Call Visited
• Call Completion % = Call Visited / Call Plan
• Call Plan have order
• Strike % = Call Plan have order / Call Plan
• Call Unplan Order
• Unscheduled % = Call Unplan order / Call Plan
• Un-Visit
• Actual Visited
• Effective Call
• Effective Call %

[Ashish_Mathur]

Hi,

That much information is not enough for me to help you.  I am sure someone who understands your question will be able to help.

[ADSL]

Hi @Ashish_Mathur, @Aburar_123,

Could you please help to support and suggest of this post? Thank you in advance. 

Best Regards,

[MFelix]

Hi @ADSL ,

Create the following measures:

Plan = 
VAR Cust_Route_Values =
    SUMMARIZE (
        CUST_ROUTE_PLAN,
        CUST_ROUTE_PLAN[CUST_CODE],
        'Sales Rep'[Sales Rep Code],
        'Calendar'[Date],
        "id",
            CUST_ROUTE_PLAN[CUST_CODE] & 'Sales Rep'[Sales Rep Code] & 'Calendar'[Date]
    )
VAR ID_Table =
    DISTINCT ( SELECTCOLUMNS ( Cust_Route_Values, "ID_Cust_Route", [id] ) )
VAR Daily_Time_Values =
    FILTER (
        SUMMARIZE (
            DAILY_TIME_SUMM,
            DAILY_TIME_SUMM[CUST_CODE],
            'Sales Rep'[Sales Rep Code],
            'Calendar'[Date],
            "ID",
                DAILY_TIME_SUMM[CUST_CODE] & 'Sales Rep'[Sales Rep Code] & 'Calendar'[Date]
        ),
        [ID] IN ID_Table
    )
RETURN
    COUNTROWS ( Daily_Time_Values )

Unplan = [Total Actual Call] - [Plan]

[ADSL]

Hi @MFelix,

Thank you for your feedback/advise.

There are 02 parts of the report that I am looking for:

>>1st part, we can get the report result from Cust_Route_Plan like --- Call Plan, Call Visited, Un-Visit

>> 2nd part, we can get the report result from Daily_Time_Summ like --- Total Sales Order, Call Plan Order, Call Unplan order

Result Expectation

Any suggestion/advise? Thanks a lot

[MFelix · Accepted solution]

Hi @ADSL  

Sorry for the delay in the answer but was away the last days.

Add the following metrics to you model:

Actual visited = COUNTROWS(FILTER(ALL(DAILY_TIME_SUMM), DAILY_TIME_SUMM[CALL_IND] <> BLANK()))

Call completion % = DIVIDE([Call Plan], [Call Visited])

Call Plan = COUNTROWS(CUST_ROUTE_PLAN)

Call Plan Order = 
VAR Cust_Route_Values =
    SUMMARIZE (
        CUST_ROUTE_PLAN,
        CUST_ROUTE_PLAN[CUST_CODE],
        'Sales Rep'[Sales Rep Code],
        'Calendar'[Date],
        "id",
            CUST_ROUTE_PLAN[CUST_CODE] & 'Sales Rep'[Sales Rep Code] & 'Calendar'[Date]
    )
VAR ID_Table =
    DISTINCT ( SELECTCOLUMNS ( Cust_Route_Values, "ID_Cust_Route", [id] ) )
VAR Daily_Time_Values =
    FILTER (
        SUMMARIZE (
            DAILY_TIME_SUMM,
            DAILY_TIME_SUMM[CUST_CODE],
            'Sales Rep'[Sales Rep Code],
            'Calendar'[Date],
            "ID",
                DAILY_TIME_SUMM[CUST_CODE] & 'Sales Rep'[Sales Rep Code] & 'Calendar'[Date]
        ),
        [ID] IN ID_Table
    )
RETURN
    COUNTROWS ( Daily_Time_Values )


Call Unplan Order = [Efective Call] - [Call Plan Order]

Call Visited = CALCULATE([Call Plan Order], ALL(DAILY_TIME_SUMM[ORD_IND]))

Efective Call = COUNTROWS(SUMMARIZE(DAILY_TIME_SUMM,DAILY_TIME_SUMM[CUST_CODE], DAILY_TIME_SUMM[CALL_DATE]))

Effective call % = DIVIDE([Efective Call], [Actual visited])

Strike Rate % = DIVIDE([Call Plan Order], [Call Plan])

unschedulle % = divide([Call Unplan Order], [Call Plan])

Unvisited = [Call Plan] - [Call Visited]

Result below and in attach PBIX file:

Once again I apologize for the late response have a great 2023.

[ADSL]

Hi @MFelix,

Thank you for your helpful measure.

Is it possible to get the same result without choose the slicer "E" from ORD_IND? "E" refer to call have order that it's applied to "Call Plan Order" & "Call Unplan Order" and "Effective Call = Call Plan Order + Call Unplan Order"

If can, so then we don't need to add the slicer "E" for user to select/choose for the report. 

Please kindly check and advice the suggestion.

Thanks and Regards,

[MFelix]

Replace the metrics below by:

Efective Call = COUNTROWS(CALCULATETABLE(SUMMARIZE(DAILY_TIME_SUMM,DAILY_TIME_SUMM[CUST_CODE], DAILY_TIME_SUMM[CALL_DATE]), DAILY_TIME_SUMM[ORD_IND] = "E"))


Call Plan Order = 
VAR Cust_Route_Values =
    SUMMARIZE (
        CUST_ROUTE_PLAN,
        CUST_ROUTE_PLAN[CUST_CODE],
        'Sales Rep'[Sales Rep Code],
        'Calendar'[Date],
        "id",
            CUST_ROUTE_PLAN[CUST_CODE] & 'Sales Rep'[Sales Rep Code] & 'Calendar'[Date]
    )
VAR ID_Table =
    DISTINCT ( SELECTCOLUMNS ( Cust_Route_Values, "ID_Cust_Route", [id] ) )
VAR Daily_Time_Values =
    FILTER (
        SUMMARIZE (
            DAILY_TIME_SUMM,
            DAILY_TIME_SUMM[CUST_CODE],
            DAILY_TIME_SUMM[ORD_IND],
            'Sales Rep'[Sales Rep Code],
            'Calendar'[Date],
            "ID",
                DAILY_TIME_SUMM[CUST_CODE] & 'Sales Rep'[Sales Rep Code] & 'Calendar'[Date]
        ),
        [ID] IN ID_Table && DAILY_TIME_SUMM[ORD_IND] = "E"
    )
RETURN
    COUNTROWS ( Daily_Time_Values )

This should give expected result without the slicer.