Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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,
Solved! Go to 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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
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.
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.
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.
Hi @Ashish_Mathur, @Aburar_123,
Could you please help to support and suggest of this post? Thank you in advance.
Best Regards,
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]
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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,
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português