The end client desires to upgrade the sales reporting for its resources from the current MS Excel spreadsheets to an online system.
The requirements of the end client were as follows:
1 .) Consolidate the data from multiple spreadsheets for analysis over period in time.
2.) Provide visibility for sales and expenses by route and truck .
Step 1: the ETL team mapped the spreadsheets onto a database(initially MS Access) for easy data entry at the client main location for after trip and before trip details. Attached are the details for the same (appendix a)
Design of the audit report:
As a first step the ETL team replicated the audit report for the end client in Powerbi. The purpose of replicating the audit report was as follows:
1.) Check whether the numbers are correct as per the data entry done for the end client
2.) Consolidate all the audit numbers for the end client in one interactive framework.
The details of the replication of the audit report are in appendix b.
Development of analytics and dashboards: the ETL team identified four(4) key kpis for analytic purposes.
· Sales: date, truck and route
· Returned sales-returned crates, returned full bottles, returned empty bottles: date, truck and route
· Samples: date, truck and route
· Expenses: date, truck, route and expense type.
The behavior of expense is as follows:
· Aggregate by date dimension and expense type
· Semi aggregate by route and truck master
· Non aggregate by item dimension
The behavior of sales is as follows:
· Aggregate by date dimension
· Aggregate by truck and route master
· Non aggregate by expense type
· Aggregate by item entities.
In case of expenses analysis the analytics to be developed were to be grouped by expense type(that does not fall in direct hierarchy with route and truck hierarchies). Hence the calculated measures need to be fixed at each level of analysis by expense type( for route truck and overall analysis).
Accordingly the etl team developed the following kpis for the expense analytics:
Expense(fixed overall):
All expense: CALCULATE(SUM(after_trip_allocation[expense]),ALL(after_trip_allocation),after_trip_allocation[type_of_expense_id]>0)
NOTE: The type of expense id was stipulated to be more than 0 since without the expense id expense aggregate would not make sense.
Expense across route id and expense nature:the given measures fixes the total expense
EXPENSE_ACROSS_ROUTE_EXPENSETYPE = CALCULATE(SUM(after_trip_allocation[expense]),ALLEXCEPT(after_trip_allocation,after_trip_allocation[route_id],'type of expense'[expense_nature]), after_trip_allocation[type_of_expense_id]>0)
Percentage calculation for route:
PERCENT_ROUTE = (after_trip_allocation[EXPENSE_ACROSS_ROUTE_EXPENSETYPE]/after_trip_allocation[expense_excl_nature])*100
Expense across truck id and expense nature:
EXPENSE_ACROSS_TRUCK_EXPENSE_NATURE = CALCULATE(SUM(after_trip_allocation[expense]),ALLEXCEPT(after_trip_allocation,Truck_master[Truck_ID],'type of expense'[expense_nature]),after_trip_allocation[type_of_expense_id]>0)
Percent expense along the truck route is defined as follows:
PERCENT_TRUCK = (after_trip_allocation[EXPENSE_ACROSS_TRUCK_EXPENSE_NATURE]/after_trip_allocation[expense_excl_nature])*100
Attached is the dashboard for the expense analysis:
Calculation of sold bottles:
Sold bottles is the difference of quantity loaded,return bottles and sample bottles.
Attached is the snapshot for the sold bottles
sold_bottles =
SUM('Before_trip_allocation'[quantity_loaded]) - SUM('after_trip_allocation_item'[Returned_bottles])-CALCULATE(sum(after_trip_allocation_item[returned_full_bottles]),after_trip_allocation_item[sample_flag]="'sample'")
Sold bottles are calculated on the basis of:
· Vehicle no.
· Route description
· Date dimension
· Item dimension(brand,volume and holding type)
Attached is the dashboard of the sold bottles:
Vehicle no and the route description is the slicer .we can analyzed the sold bottles for the particular vehicle and route.
Calculation of sample bottles
Sample bottles is extracted from the sample flag.
Attached is the snapshot for the sold bottles
Sample = CALCULATE(sum(after_trip_allocation_item[returned_full_bottles]),after_trip_allocation_item[sample_flag]="'sample'")
Sample bottles are calculated on the basis of:
· Vehicle no.
· Route description
· Date dimension
· Item dimension(brand,volume and holding type)
Attached is the dashboard of the sample bottles:
Calculation of return bottles:
Return bottles is extracted from the sample flag.
Attached is the snapshot for the sold bottles
Returned_bottles = CALCULATE(sum(after_trip_allocation_item[returned_full_bottles]),after_trip_allocation_item[sample_flag]="'returned'")
Return bottles are calculated on the basis of:
· Vehicle no.
· Route description
· Date dimension
· Item dimension(brand,volume and holding type)
Attached is the dashboard of the return bottles:
Appendix A
LIQUOR COMPANY MOBILITY APP
The given implementation has been done to automate the field sales collection data for Liquor Company.
As a first step, the ETL team has deployed the MS Access database on the client end for data capture of before and after truck load. That is the ETL team has devised a system wherein it captures the data when the truck is loaded by various SKUs. Also the end client inputs the data after the truck has completed its journey.
The benefit to the end client is that the ms excel spreadsheets shall be replaced by database and consolidated reporting shall be available to the end client.
Access Implementation
The end client desired to have a set of reports using PowerBI and database is created in MS Access using the following features:
· Calculate the crates loaded on the basis of driver name,areaname,sales person.
· Calculate the Daily routine sales and commission report on the basis of brand ,volume and holding types.
· Calculate the total bottles sold after the trip.
Database model for Access interface is as follows:( the data model was developed by reverse engineering the client spreadsheets)
Attached is the explanation of the database model:
Truck Master: The truck master entails all the master data for the truck.It includes the vehicle no.,driver no.,helper,sales person of the truck.
Route Master: The route master entails all the master data for the route.The route master has one to many relation with the truck master.That is a truck can use the various route to travel or will have the same route on multiple dates. .It includes all the information of route i.e. when the journey started ,what was the meter reading and how many crates are loaded.
Before trip allocation: Before trip allocation entails all the information of the item before the journey was started i.e. how much quantity was loaded of which brand,volume and holding type.which type of load was it,either it is for “sales” ,”promotion”or ”sample”.
After trip allocation:it entails all the information of after trip of the truck on the given route.i.e how any full bottles,emptybottles,emptycrates,breakage bottles are returned,how much fuel was consumed and the expense details .
After trip allocation item: it entails all the information of after trip.i.e how any full bottles,emptybottles,emptycrates,breakage bottles are returned,which types of material was sold “sample”,”promotion” or “sold”.
The database is created in MS Access.There are two buttons before trip and after trip.
Attached is the snapshot :
Before trip allocation contains all the details of the truck ,when the journey was started and how much quantity was loaded of which brand ,what is the volume and the holding types.
After trip allocation:the after trip form contain the after trip allocation detail and expense detail on the basis of vehicle no. and area name.
Attached is the snapshot:
After trip allocation form contains the duration of journey and the description of the sample flag i.e either the item returned was “return” or “sample” on the basis of brand, volume and holding types.
Expense detail: This form contains the expense details of the truck.
Appendix B
Attached is the explanation for the dashboard developed for replicating the audit report.
The above portion has been mapped in PowerBI as follows
Area name and Vehicle no: These 2 have been assumed to be filter parameter as follows:
Vehicle no. and area name have been used as slicers for the given dashboards.
DAX function:The sample and returned bottles are calculate by using the dax functionality.
Attached is the snapshot:
This is the aggregate function.we use the DAX functionality for the calculation of the returned bottles
For the same the etl team generated an aggregate for ‘after_trip_allocation’
Attached is the snapshot
Cross filter sample:
In case interested in converting spreadsheets to data model, please contact the following:
Name: Apoorv Chaturvedi
Phone: +91-8802466356
email: apoorv@mnnbi.com
Blog prepared by:
Pinky Singh: https://www.linkedin.com/in/pinky-singh-a77a4a180/
No comments:
Post a Comment