Build an end-to-end Power BI logistics analytics project covering domain understanding, semantic modeling, DAX calculations, report building, and deployment pipelines for interview prep.
Key Takeaways
- Business understanding and standardized DAX calculations are as important as chart-building for a BI developer role.
- A single trusted semantic model prevents conflicting numbers across departmental reports.
- Gold-layer raw data usually needs DAX measures to answer SLA, cost, and performance questions.
- Deployment pipelines let teams promote Power BI content reliably from development to production.
What the video covers
- Presents a real-world logistics analytics project for aspiring Power BI and BI developers, framed around the fictional freight company Apex Logistics Group.
- Covers five areas: logistics domain, semantic model, DAX calculations, report building, and deployment pipelines.
- Describes business problems: fragmented, untrusted data across departments, inconsistent numbers between reports, and manual Excel workarounds for SLA penalties and fuel charges.
- Explains the Microsoft Fabric medallion architecture (bronze/silver/gold layers) and the need to keep PII out of the reporting layer.
- Identifies data quality issues such as roughly 2,485 shipments missing delivery dates and shipment dates earlier than order dates.
- Walks through the gap between raw gold-layer data and the business questions executives ask, such as on-time percentage, SLA breaches, and revenue by transport mode.
- Demonstrates creating calculated columns, DAX measures, and calculated tables, and explains that averaging percentages or per-kg values requires proper weighted calculations.
- Shows building report visuals including KPI cards, on-time percentage targets, total revenue by transport mode, and slicers for extra days late.
- Discusses connectivity modes including Direct Lake, Import, and DirectQuery, and how to model relationships between tables.
- Ends with deploying Power BI items to production using deployment pipelines across environments.
Chapters
Full Transcript — Download SRT & Markdown
Speaker A
You will get the job if you work on this logistics domain project. Hi. Welcome to Real Data Cloud. Want to become Power BI developer?
Speaker A
Don't just learn DAX. Build a real-world logistics analytics project that you can confidently explain in your next interview.
Speaker A
In this project, we are going to cover five important areas. One, logistics domain. Two, semantic model.
Speaker A
Three, implementing DAX calculations. Four, report building. Five, deployment pipelines. This is not just creating charts. It's about understanding the business, implementing the right calculations, and the building a complete analytics solution. If you are preparing for Power BI developer or
Speaker A
BI developer role, this project will help you understand [music] how real-world analytics solutions are built. So, let's get started.
Speaker A
Before we begin, please, please, please subscribe to my YouTube channel. Your subscription and support genuinely encourage me to create more real-time projects, practical tutorial, and valuable content for data community. If you find this project helpful, don't forget to like
Speaker A
the video and share your thoughts in the comments. Also, follow Real Data Cloud on LinkedIn, Instagram, and join our WhatsApp community for more projects, updates, learning resources, and announcements. Thank you so much for supporting Real Data Cloud. Don't just
Speaker A
learn. Build. So, what is the main problem statement? Okay. So, there are few things in the organization that each and every organization is expecting from the data engineering and BI team that they should collaborate with each other, and they should deliver the project.
Speaker A
So, what is main thing here? So, Apex Logistics Group it's a freight company. So, where uh 4.2 million shipments a year every year.
Speaker A
So, with a parcel volume which is growing 18% year-on-year growth. So, what do we have here? So, the data source is the data is getting generated from the Oracle ERP. There are 30 carriers.
Speaker A
Lane and fuel rates that comes every month. That is being stored that is being saved by their team. It's not in the centralized repository. And there is a carrier SLA.
Speaker A
So, it should be delivered within a week or whatever the service level agreement that they have from the source to destination. It should be delivered in the proper timeline.
Speaker A
But, what is the problem that they have? Apex Logistics need one trusted governed source of free data.
Speaker A
So, what is one trusted? So, So, let me tell you the what is the main problem that the organization face.
Speaker A
CFO, CEO, or CIO, or CTO. Or the vice president, general managers, what is the main drawback that they face is yes, they are they are investing money on a project.
Speaker A
Okay? But, the thing is that the project is not getting delivered. So, each and every team the supply chain there is a general manager.
Speaker A
The HR team there is a general manager. And if you take marketing team there is a general manager. Every time what do they do? Each and every individual general managers are the people who are working under the general manager. The
Speaker A
analyst are going to send the information in the Excel or in the PPT to the higher management, vice presidents, or to the CEO, CIO, CFO.
Speaker A
So, the marketing team is sending one file. And the sales team are sending one file another file. But, there is no matching in between the marketing and sales team.
Speaker A
They have invested $1 million. Okay? but the sales are only $1.2 million. So, where this $1 million has been dropped?
Speaker A
So, there is a no trusted data governance. Whatever the data that is generated from the source should be equivalent. They should not the each and every individual department should not generate the data.
Speaker A
That is the main main thing. Okay? So, that is the main thing. So, what is that zero data loss?
Speaker A
Should be ready within 30 minutes of the close. Full audit and lineage. So, whenever the audit team comes into picture, they should audit the data clearly.
Speaker A
And the next one is PII. Personally identifiable information should be kept out of the reports.
Speaker A
It should be in the source. It should be in the silver layer, or it can be in the golden layer, but it should not be into the reporting layer.
Speaker A
And the next one is what did we build? So, we built the medallion architecture in Microsoft Fabric. The next step that we need to build is like a golden data sets.
Speaker A
Okay, this is a fascinating terminology that we have, which is called as a golden data sets.
Speaker A
So, the next step is from golden tables. So, we need to go for building the semantic model.
Speaker A
So, what is the questions that the CFO is asking? Or the vice presidents they are asking is are our carriers on time or not?
Speaker A
Who breaks their SLA? And what does it cost? So, how much fuel do we bill?
Speaker A
So, in the organization, what do they do? So, they they may or may not bill something.
Speaker A
If they are spending $1,000, but that is not billed. Then that is a loss. So, when the audit people come and they ask where this there is a there is a spend of $1,000, but I do not see any bill on that
Speaker A
$1,000. How do the analyst or the sales manager or the marketing manager, they are going to answer this?
Speaker A
Which shipment need attention first? Out of 5,358 shipments, whether how many of them are delivered on time, how many of them are lagging, which carrier delivered faster, which carrier did not deliver, which carrier broke that SLA.
Speaker A
So, that's all needed. Every one of these needs a calculation the gold table don't have.
Speaker A
What is the BI problem? So, the BI problem has raw columns only. So, gold has dates and amounts, not the answer.
Speaker A
And whether it is on time or breached column. So, we need to create the DAX measures, DAX columns, calculated columns.
Speaker A
Next, the logic should be reinvented. So, each analyst, so there would be like we are working on a logistics and supply chain. There are two teams involved.
Speaker A
Logics logistics teams is involved, operation teams involved, supply chain involved, and the carrier team involved.
Speaker A
So, there are multiple teams involved. So, everybody is calculating their own way. It should not happen like that.
Speaker A
There should not be like each analyst is going to calculate these things. Standard calculations.
Speaker A
Two reports, two different numbers. So, that's another problem. And there are like wrong tables. What are the wrong tables? So, if you go average percentage gives wrong percentage. And package values can't simply be averaged.
Speaker A
Okay? You are shipping something. So, what do we do if you want to ship something or if you want to go for We go to the courier. And we want to What is that from India to US? We send many
Speaker A
products. So, what is the kg? Okay? So, if you go $1 per kg, it should be $1 per kg. It should not be $1.5. No standard calculations.
Speaker A
Blank traps. Okay? So, what is that? 2,485 shipments have no delivery date. So, every date should be included. What is that? What are the main when you go to when you work on the logistics or any project?
Speaker A
So, there would be like order date, shipment date, estimated delivery date, and actual delivery date. So, here out of 50% probably like 40% of the data does not have the delivery date.
Speaker A
And what is that? Blank four. Four fake early shipments. So, what is that? Early shipments?
Speaker A
Today we ordered 19th October 2026. We ordered a product, but the shipment date is 18th October 2026. That's another problem.
Speaker A
Okay? So, the shipments dates are earlier. And what is that? Excel work around. SLA SLA penalties worked out in the Excel.
Speaker A
So, there is a carrier. What are they doing? They are trying to They are miscalculating something. So, the billings are provided miscalculation.
Speaker A
And the penalties have been changed. Okay? No standards in here. Fuel added by hand every month.
Speaker A
For example, if I change something, instead of 1.002, I just made 1.0025. Okay? There is a change in the fuel charges.
Speaker A
No drill down. Can't slice by carrier type on priority. Exceptions are found too late.
Speaker A
So that's problem. And what do we have? There are eight questions that CFO is asking or general manager is asking.
Speaker A
Did we deliver? Logistics manager is asking. Did we deliver when we promised what we promised? Okay, what is that? It is the KPI. KPIs are like key performance indicators.
Speaker A
Whether it is on time or not. And the next one, how many days late or early?
Speaker A
Average delay days. Did the carrier break its SLA? SLA breached and what is the penalty percentage? Penalty amount. One status labeled for the report. On time, breached, in transit. So, we need to label that. Okay, what do we need to
Speaker A
label? So, we need to create some conditional condition stating that it is on time.
Speaker A
Whether it is bearish breached or in transit. In transit is like it is traveling. The product is traveling.
Speaker A
Okay? And how long did the trip takes? Average transit days days. Is the route slower than the plant? Days versus route plan.
Speaker A
How much fuel do we bill and what is the total? Fuel surcharge and total revenue.
Speaker A
Who pays better per kg? Who is first? Revenue per kg and priority band that we need to create.
Speaker A
So, what do we have? 23.8% on-time delivery. And the 1551 SLA breaches. So, 5.6 million dollars SLA penalties.
Speaker A
And 19.9 million dollars fuel charge surcharges built. And 2,485 does not have the delivery.
Speaker A
I mean it it it might have delivered but no date. So, that's a problem.
Speaker A
Okay? So, what do we need? Build one Power BI model. Every KPI is defined once.
Speaker A
And the answer come back in seconds. And every report shows the same number. One definition. Answer in seconds. Same number.
Speaker A
So, whatever the number that they are looking each and every team, it should be same in the reporting also.
Speaker A
Okay? So, that is what we have it. As a BI developer or as a data analyst, how do you implement or how do you work on this project?
Speaker A
So, this is about the problem statement. So, now let me put the direct link semantic model. So, let me put as an active and semantic model fact and dimension table. So, let me make it active.
Speaker A
So, the next one is what is the semantic model? So, what is semantic model?
Speaker A
Now, let us see one by one. So, semantic model, it is a business layer between the go- golden tables, golden data sets, and the report.
Speaker A
So, it is like a processing of the data. Yes, we got the golden tables. We got uh one fact table and multiple dimension tables. But, if you want to work on the project, if you want to work on the reporting side or
Speaker A
into the AI, or implementing the machine learning models, so what do we need? There is a linkage between these tables.
Speaker A
So, it knows that tables connect what every number means and how to calculate this.
Speaker A
So, what do we have in the tables? In the gold tables, there are few tables.
Speaker A
Fact shipments, carrier, customer, route, and dates. So, if you want to know the number, and if you want to know the relation between the numbers. So, what do we need to do? So, we need to build the semantic model.
Speaker A
So, what do we have in the semantic model? Relationships, measure names, security. So, what does it produce? So, it produces Power BI report, carrier scorecard revenue. So, this data can be used in the Excel, analyze in Excel pivot table, and the same data can be
Speaker A
used in the co-pilot question and answers. You can ask about the questions. So, every report reuse it. So, older it is called as a data set. The newer one it is called as a semantic model. But, what does the semantic model hold?
Speaker A
So, what do we have in the semantic model? The semantic model has the tables, fact tables and dimension tables.
Speaker A
It can have the relationships. How do we make the relation? What is that? How do we link these tables?
Speaker A
So, if I want to know the carrier data, shipments by carrier, so how do I link the number the measures from the the values from the fact table, and how can I get the carrier information in the carrier table?
Speaker A
Next is measures. The semantic model has the measures of the DAX information calculations, calculated columns, and the measures.
Speaker A
Next one, calculated columns. So, we can create a conditional columns based on the columns that we have.
Speaker A
Next one, hierarchies. So, we can go for the hierarchies. What is that? If you consider if you consider the dates, year, quarter, month, day. If you consider the region, what do what would we what we have it in the region?
Speaker A
Region, country, state, cities. This is the hierarchy. Next one, names and formats. So, what is that names and format? If the data or a column is in the number format, we can change it into text details. So, that is naming. If you get
Speaker A
into the reporting side, what do we do? We go for making it as a revenue or the sales, year-on-year growth. Okay? This is a reporting terminologies.
Speaker A
Next one, row level security. What is a row level security? So, we go for each and every employee working in the organization, he would have the roles and responsibilities, the designated role.
Speaker A
If you are the manager of Mumbai or you are the manager of Hyderabad or South India or South region. So, what we restrict the data to that manager only see that South region's data.
Speaker A
You should not see the North Indian data or North region data. So, that is we implement the row-level security.
Speaker A
And the next one is a description. So, what do we do? We create a data modeling between the tables. Whatever the relation that we make in between the tables, that is called as a data modeling. So, when we make the data
Speaker A
modeling Okay? So, if you What is this? In the sema- the semantic model, I can simply uh what is that uh in the layman terminology, what do I do? I can make it as a family.
Speaker A
Okay? If you take If you take Salaar movie, okay? What is that? The Shouryanga's and another team. So, it is been divided. So, what is that again in the Shouryanga, there are few people.
Speaker A
So, what is that? Whom do you vote? There is a What is that? There is a I can't remember the exact word. Okay?
Speaker A
Whom do you want to vote? So, it it is been calculated. So, the Kadiyam he takes that Kadiyam and you you go for voting something. So, semantic model is also like that.
Speaker A
What is the relation of that hero character with Prabhas' character with Prithviraj? So, how can this Prabhas this hero is going to maintain something? Okay? That is what a semantic model in the layman terminology.
Speaker A
So, why it is useful? Okay? One version of truth. So, you can build once. If you build this semantic model, this semantic model is being used in different domains, different departments. Okay?
Speaker A
And the next one is business friendly. So, the technical terminology, there will there will not be any technical terminology. You can say that revenue or a carrier name.
Speaker A
Instead of making underscore name or the combination of two words, what do you say? You go for just naming business friendly names.
Speaker A
Okay? There is no need of writing the joins again and again. Fast. Already we processed something.
Speaker A
Okay? it is just like a aloo patty or paneer patty. What do you need to do?
Speaker A
You just take that and deep fry in the oil. That's it. There is no need of preparing it from the beginning. Already it has been prepared.
Speaker A
Okay? And security in one place. The next one is question and answer ready. We can give this data to the co-pilot or any agent AKI.
Speaker A
So, without a model every report rebuilds the join and formulas. This is how East and West region got different answers.
Speaker A
What is a relationship? Okay? So, in the data modeling, we have something called as a relationship.
Speaker A
As an individual, what is my relation with my father or with my mother or with my wife? Okay, as an individual. So, relationship a link between two tables on a shared columns.
Speaker A
Okay? For example, if you take me, I can become son to my what is that to my father.
Speaker A
I can also become brother to my sister. Okay, the same person. How what is the relation? Okay? It lets a filter one table reach other table.
Speaker A
So, what do we see What do we see here? So, we have a fact table and it is surrounded by the dimension tables.
Speaker A
So, how do you make the link in between these tables? What is that? So, we have one-to-many relation between these tables.
Speaker A
Okay? So, what is that one-to-many relation between the tables? If you take one example, we have something called as a dim carrier.
Speaker A
So, fact shipment and there is a relation between the dim carrier. How they are linked between these two tables? The carrier ID.
Speaker A
Okay? In the dim carrier, carrier ID is the primary key. Okay? So, primary key what does each and every record is being identified by one unique ID called as a carrier ID. No duplicates. Carrier table does not have
Speaker A
any duplicates. Fact tables can have the related information. We cannot call it as a duplicates, but it can have many shipments. For that particular carrier ID, it can have multiple shipments.
Speaker A
Okay? So, we have many to one relationship. Single unique record in a dimension table, but multiple records in the fact table.
Speaker A
So, that is a relationship. So, what happens when we try to filter the data based on the carrier ID 001, the fact table would be filtered.
Speaker A
Okay? The fact table would be filtered. At the time of practical, we will discuss. Don't worry. So, let me go to the next.
Speaker A
So, what are all the types of relationships that we have? The first type of relationship, it is called as a cardinality in Power BI.
Speaker A
Okay? In the semantic model. One-to-many relationship. One of the most important relationship that is being each and every golden layer that needs the semantic model needs is one-to-many relationship.
Speaker A
What is a one-to-many relationship? The dimension table would have a unique primary key. Each and every record is being identified by one key. So, it has a unique records. No duplicates.
Speaker A
But, whereas the fact table would have multiple related records. Then, this is called as a one-to-many records. One-to-many relationship. One carrier can have many shipments. So, that is one-to-many relationship.
Speaker A
Next one is many-to-one relationship. The same link read from the fact side. Many shipments, one carrier.
Speaker A
Okay? What is that? Many shipments, one carrier. Next one is one-to-one relationship. What is that one-to-one relationship? In the fact table, in the dimension table, there is a carrier 001. In the fact table, there is only one transaction about
Speaker A
that carrier. Okay? Each row matches to the one row. So, it is a rare condition.
Speaker A
One-to-one is just like a rare I mean, we do not make we will not be getting much when we try to join the fact to fact tables or when we go for building the bridge tables or when we go
Speaker A
for uh semi fact table and fact table, we go for choosing one-to-one relationship. Next one is many-to-many relationship where each and every organization, they do not want to make the many-to-many relationship.
Speaker A
Both sides dimension table or semi dimension table or semi fact tables have the many records and also the fact table also has the many records.
Speaker A
Multiple related records. So, that is many-to-many relationship. So, what do we have again? We have something called as a active versus inactive.
Speaker A
When there is a straightforward line, then that then that is an active relationship. When there is a dotted lines, there is an inactive relationship, but by using relationship function dash, we can go for making a relation inactive indirect relation between the
Speaker A
tables. Next one is a cross filter direction. What is a cross filter direction? So, how do you want to filter the data?
Speaker A
Simple question. Okay? How do you want to filter the data? So, if you go okay? If you go here, so, we have two tables, carrier and shipments.
Speaker A
Do you want this shipments to shipments table to be filtering the dimension carrier or carrier to be filtering the shipments table?
Speaker A
In general, the practiced methodology is cross filter direction be single. The dimension table should filter the fact table. Always remember.
Speaker A
Okay? Carrier table should filter the fact table, but shipment table should not filter the carrier table.
Speaker A
That is a general format that we implement. Next, both direction. Do you want this shipment table?
Speaker A
Every project that we work, our main focus is on the transactional data only. Okay? So, carrier slicer filter filters the shipments.
Speaker A
Okay? Status law lost also shrinks the carrier list. Okay? That is also both direction. We will see We will see how do we need to implement the relation between the tables.
Speaker A
And also the cross filter direction. Okay? What is that rule of thumb or thumb rule is keep it single.
Speaker A
Okay? Keep it single. So, how do we build the OPEX semantic model in the fabric? So, we can create this and we can pick the tables, link them, and make each line.
Speaker A
Okay? So, this is how we are going to implement the semantic model. Now, let us get into the practical.
Speaker A
Okay? Now, let us get into the practical. So, how do you see if you see what do we have in this OPEX logistics lakehouse?
Speaker A
Let me expand this. So, we would be having different schemas. What are all the schema that we have? We have a DBO default schema, bronze schema, gold schema, and silver schema.
Speaker A
What we are trying to build now? Golden data sets. So, let me expand the gold table. What do we have? Carrier table, customer table, date, route, and fact table.
Speaker A
So, how do we build the semantic model? Let me click on the new semantic model.
Speaker A
So, once we click on new semantic model, we get all the tables over here.
Speaker A
So, let me only select the gold tables. Okay? And give the name. Okay, what is that? OPEX OPEX logistics semantic model.
Speaker A
And let me click on confirm. So, we are selecting the golden uh What is that schema? So, whatever the tables under the golden table, we would get it. Golden schema. So, what we have there is a checkbox. So, what we can do,
Speaker A
we can uncheck. For example, we do not want to expose a table directly in the reporting or in the golden data sets. Or there is a confidential information in a particular table, we do not want to expose that
Speaker A
table to the artificial intelligence or to the agent AI or to the machine learning models or in the BA business intelligence, what do we do? We uncheck that table.
Speaker A
10 tables or 20 tables available, we uncheck that table and we go for what?
Speaker A
What do we do? We go for building the semantic model. Now, what do we see?
Speaker A
Now, you see this is this looks similar in the Power BI. The data modeling part in the Power BI.
Speaker A
So, what do we have in here? So, now you see this is a view.
Speaker A
Even if I open Power BI, we would see the same view. Okay? The same thing.
Speaker A
All the tables model view, DAX query view and we also have TMDL view. Okay? TMDL So, let me go to the model view. So, what do we see here on top right side, there is an option called as
Speaker A
editing and viewing. So, we cannot change. And I can also go to edit in desktop. For example, if I click on desktop and let me click on Power BI for example.
Speaker A
Okay? If I click on Power BI, so what is the data? Okay, what is the mode? What is the connectivity mode that we are going to connect is direct lake.
Speaker A
Okay? So, previously we had a direct query mode import mode and live connection. That the fourth connectivity mode is a direct lake. What is a direct lake? We from Power BI, now you see we got the same tables in the Power BI. How wonderful it
Speaker A
is. Okay, we got the same tables in the Power BI. Now what do you see? This is a direct Now if I hover over anything the name of the table, source name, what is the source? Apex gold schema, storage
Speaker A
mode. You see the storage mode. Previously we have import mode and direct query mode. Now the storage mode is being changed into the direct lake.
Speaker A
Data source lakehouse, server name Apex logistics lakehouse. What is the workspace and when the data is refreshed?
Speaker A
Okay, when the data is refreshed. I can go for making the relation between the tables.
Speaker A
But now let us go to the same thing over here. Continue here. So what do we see? How do we make the relation between the tables?
Speaker A
You have an option called as manage relationship. Click on this. Click on the manage relationship.
Speaker A
Now you see new relationship, click on that. Always remember, another thing to remember is from table should always be the fact table.
Speaker A
Two tables should should be the dimension table. Okay, now if you see the preview is not enabled.
Speaker A
We are not able to see the data. Why? Because it is a direct lake data. Let me go to Power BI. Now you see, what do we have here?
Speaker A
No report view. Now you see here, there is no report view, only model view and DAX view, team view.
Speaker A
We are not able to see three views. What is that? Report view, table view.
Speaker A
Why the report view and table view are not seen? Because it is a live connection.
Speaker A
Probably it is we are connected to the direct lake, but it is a live connection.
Speaker A
So if you remember from the Power BI concept there are three three views, three connectivity modes. In the import mode we can see three views. What are all the three views? Default views. Report view table view, and model view.
Speaker A
And in the direct query mode, we can see we cannot see the table view.
Speaker A
We can see the report and the model view. In the live connection, we can only see the model view. What we can do? We can only make the relation between the tables.
Speaker A
Okay? Here also the same thing. Now you see carrier ID from the shipment table.
Speaker A
Let me come down. Now what do you see? Cardinality and cross filter direction. What is that? Many to one relationship.
Speaker A
And one more thing to remember is that cardinality we cannot change the cardinality. It is It is going to automatically identify based on the availability of the data in the table.
Speaker A
If there are multiple related records, multiple shipment IDs, it identified as a many. And dim carrier has only each and every record has a unique carrier ID, so that is the reason it identified as a many to one relationship. I cannot change one to
Speaker A
one. Okay? I do not have any possibility. Okay? And I cannot go for many to one or one to many, many to many.
Speaker A
Okay? So, let us go with the many to one relationship. Cross filter direction, both.
Speaker A
Let me go for single direction. Let me click on save this. So, we made a relation between the fact shipment table and carrier table.
Speaker A
This is one way. The second way of making the relation, okay? What is that? Let me go for making this as a star schema, look like a star schema. What is look like a star schema?
Speaker A
Okay? What is look like star schema? Fact table is surrounded by the dimension table. Let me click on collapse. Do we have that collapse?
Speaker A
Uh yes, we have the collapse option. Let me click on this. So, I can collapse it.
Speaker A
Okay? What is that? Fact table is surrounded by the dimension table. I can put it over here. But let me just expand to see the data.
Speaker A
Okay? Not much, but let me go for this. A little bit. Okay? So, let me also do the same thing over here.
Speaker A
Okay. So, the second method of making the relation between the tables is drag and drop.
Speaker A
You see the lake house symbol. Okay? How can I drag and drop? I want to make the relation between the customer What is the column that we have?
Speaker A
Customer key. Drag and drop on the customer key in the customer table. Let me do that again.
Speaker A
Customer key and customer key. And let me click on save this. Okay. The next one is date key. Do we have the date key in here? I think we do not have any date key.
Speaker A
But, what do we do? We go for making a relation between the shipment date.
Speaker A
And the date. Shipment date and the date in the dim date. Okay, let me click on save this.
Speaker A
And the next one is next one is next one is What is that? Route ID, I think. Lane ID and route ID.
Speaker A
Let me go for route ID. Route ID and route ID. Let me come down. And let me click on save this.
Speaker A
Okay. So, we made a relation between the tables. Okay? So, we built the relation. Data modeling part is completed. Only the first part is completed. What is that first part? Data modeling part is completed.
Speaker A
So, how does the data work? Okay, what is the next question? How does the data is going to filter in all the tables?
Speaker A
If you observe here, there is no relation between the carrier and customer. There is no relation between the customer route. No relation between the route and date. No relation between the carrier and date. Again, again it is a vice
Speaker A
versa. So, if I go here, the arrow is like this. Okay, arrow is like this. For example, if I go in the next way, no relation between these two. No relation between these two. No relation between these two. No relation between these two.
Speaker A
Again, cross no relation between these two. No relation between these two. Okay? So, this is another way.
Speaker A
But, how the data is filtered? Okay? How the data is filtered? So, what do we do? So, let me So, now you see direct lake semantic model.
Speaker A
So, previously we had we have direct query. If you remember. So, now what do we have? We have direct lake.
Speaker A
So, whatever the data that we connected to the database, then that is called as a direct query.
Speaker A
Okay? So, the query whenever there is a refresh of the Power BI report, so that refresh is directly going to hit the database.
Speaker A
So, whatever the data that is updated in the database, it is going to automatically reflect in the report.
Speaker A
Okay? That is direct query. Now, what do we see? We see direct lake. The data that can be pulled into or pushed into the lake house, we can see we can get the data. We have another uh connectivity mode called as a direct
Speaker A
lake connectivity mode. So, let me write OPEX logistics semantic model. Okay, let me just prefix with uh new.
Speaker A
Okay, new OPEX logistics semantic model. So, what do we need to do here? We need to select the schema and the tables under the scheme.
Speaker A
Okay? So, what we are trying to do? So, we are trying to create the semantic model.
Speaker A
Yes, we did. We already did that in the previous session, but I implemented calculations in the in that semantic model.
Speaker A
And also I build the report. But now we need to check what are all the calculations implemented.
Speaker A
Okay. Now you can interact with me. I just unmuted. Okay, I think I told me did it automatically take Is it the same one?
Speaker A
Or Let us discuss what are all the calculations that we can do. Okay? Just a second.
Speaker A
Okay. So, when I joined in the second organization, so I expected, okay, I I had a discussion with my manager.
Speaker A
So, my manager showed me something on the cloud. So, Sharath, this is the cloud. Okay, this is what we have in the cloud.
Speaker A
So, 4 years back, this is 4 years back. So, when I saw 4 years back, there wasn't anything like this in the cloud.
Speaker A
Okay, this is what we see in the cloud, Power BI service. Actually, it is called as a Power BI service.
Speaker A
So, we were able to see only we can we can edit the report, but we cannot implement the calculations. Okay, we cannot have we cannot build the semantic model in the cloud.
Speaker A
So, if you want to build the semantic model, we need to have it in the Power BI desktop.
Speaker A
Now we can build the semantic model online in the cloud. And we can also build the report.
Speaker A
Previously, the report building wasn't there in the What is that? Complete full-fledged report building wasn't there in the cloud, but now we are able to see that.
Speaker A
Now let us see the calculations one by one. Sharath, so basically we have completed the silver layer, bronze layer, and gold layer, right?
Speaker A
Yes. Is it in Is it linked with the semantic model and with Power BI what we are doing right now just for some clarify clarification.
Speaker A
Yes. So, in the data side there are three roles. Okay, what we are discussing.
Speaker A
So, previously it was like only data engineer part. So before whatever we discussed it till now, okay?
Speaker A
Without semantic model. Okay, whatever we did. So, till gold layer the data engineer role is ended.
Speaker A
Okay, the data engineer role ended. But now what we have got is like analytics engineer.
Speaker A
Analytics engineer. Recently my one of my brothers got selected as a analytics engineer in one of the product based company.
Speaker A
So, he got questions he needs to handle, okay? In the job description I I saw the job description.
Speaker A
So, in the job description what they are expecting the person should able to handle ETL.
Speaker A
So, ETL is nothing but a data engineer part. Okay? And also he should handle data analysis.
Speaker A
Data analysis. Okay, what does the data analysis part? So, you need to know SQL, Excel.
Speaker A
And also you need to know about Python. And the next one is BI. So, he doesn't know anything about BI, but so he's also working on the BI part, okay? So, that company which of the company he got selected,
Speaker A
that they are using Power BI. So, what does so he also need to know about developing the reports.
Speaker A
Developing the reports. So, this the new role that they have named it as a analytics engineer.
Speaker A
Okay? Analytics engineer. Yes, till here that's well and good. Okay, you can learn data engineer part and you can apply for the jobs.
Speaker A
But the thing is that you the the organization is also asking about the BI part.
Speaker A
Okay? BI role also they are asking. So, we have implemented the calculations. Now, let us see what are all the calculations that we implemented.
Speaker A
First one is is on time. The first calculation is is on time. So, when you get into the BI, so there are two things two kinds of calculations in the BI.
Speaker A
Okay? The first one is calculated column. The second one is measure. So, calculated column, calculated measure, and calculated table. For all these, what do we use? We use DAX functions. DAX. What is a DAX? Data Analysis Expression.
Speaker A
So, the way we have functions in uh uh PySpark that we discussed SQL commands, so we have DAX that is specially designed for the Power BI.
Speaker A
So, there are functions if you see is blank, blank, if else condition. So, calculated What is a calculated column?
Speaker A
So, if you want to create a column based on the conditions, okay? If you remember from the previous uh sessions we discussed about uh uh creating the columns separately based on the when condition, case statements, with column. So, here also we create the
Speaker A
calculated columns. So, what is the calculated column is all about? Is on time. What is that is on time? Whether it is delivered on or before the promised date.
Speaker A
Okay? So, what is this again? One means yes, zero means no, blank means it is not delivered. So, what do we do in general?
Speaker A
So, we create a flag. What is that flag? Flag is used for filtering the data.
Speaker A
Now, you see what we are trying to do. So, we are taking the actual delivery date. This is the main function. Okay?
Speaker A
If actual delivery date less than or equal to estimated delivery date, you flag it one, else zero.
Speaker A
Okay? So, we here by this we are going to get whether it is delivered on or before the date, whatever the actual delivery date. So, this is is on time.
Speaker A
What we are trying to do in the first if else condition, first if condition, we are taking actual delivery. If it is a blank, make it blank. Then, the next else condition, we are taking this.
Speaker A
Okay? This is actual delivery is on time. And the next one is, okay, let me go for taking I think the next one is delivery days.
Speaker A
The next calculation is delivery days. So, what do we have in the delivery days? So, whether it is delivered within on time or not.
Speaker A
Okay? How many days does it take? So, what is simply how many days does it take?
Speaker A
So, it is like a difference between the estimated delivery date and actual delivery date.
Speaker A
So, how many days is it the difference between two dates? So, we have a function called as a date difference. So, it is a similar function in the PySpark. We also have the same function in the PySpark.
Speaker A
So, but the thing is that we can mention the interval over here. So day month quarter year.
Speaker A
Okay? So, how many days, how many months, how many years? So, based on the interval, we can get to know.
Speaker A
Now, we are discussing about the days, but we will get to know how many days difference between the estimated delivery and actual delivery. Simple.
Speaker A
Okay? This is second calculation. Third calculation is is SLA breached. Actually, we need to implement this business calculations in the PySpark only.
Speaker A
But, when we get into the Power BI, the calculations need to be uh defined for the reporting purpose only.
Speaker A
Okay? That is the reason we are trying to implement that. So, what do you see here? Whether it Whether the SLA is breached or not.
Speaker A
So, what is that SLA breach? Whether the delivery delay delivery days, okay? Are actually greater than maximum delay days or not.
Speaker A
Okay? So, whatever the promised days, okay, whatever the delay days, if it is greater than or greater than maximum delay days, then make it one. One represents yes, it is SLA breached. Zero represents no SLA breached.
Speaker A
Okay, based on the service level agreement, what do we do? We go for building the is SLA breached.
Speaker A
Fourth calculated column is SLA status. So, in Power BI we have two functions which are called as a if else condition, two conditional functions, and switch statements.
Speaker A
If else condition, we go for creating a conditional column based on the conditions. So, here what do we have? We use switch statement.
Speaker A
So, what is the difference between if else and switch statement? So, there is no need of repeatedly using the column again and again in the switch statement.
Speaker A
We can directly define and we can directly go for entering the variables inside that column.
Speaker A
So, if the shipment is in canceled or lost, okay, it is not delivered. And the next one, delayed, it is in in transit.
Speaker A
And the next one is in transit. And again, what we are trying to do? If it is SLA breach equal to one, then make it breached.
Speaker A
And if it is on time, make it on time. So, we have five categories: not delivered, in transit delayed, in transit, okay, breached, on time, and late within SLA.
Speaker A
Okay? So, this is SLA status. So, we can use it for filtering the data, and we can also use it in the pie chart or in the bar chart, and we can see how much amount that is being lost
Speaker A
based on the status. Next one, actual transit days. What is actual transit days? So, if you see here, so actual transit days, transit means it is in progress.
Speaker A
So, it is traveling. The product actual the shipment is traveling. The product is traveling.
Speaker A
So, in the layman terminology. Okay? So, days from shipping to delivery. Okay? Days from shipping to delivery.
Speaker A
So, this is So, how many days in between the shipment date and actual delivery date?
Speaker A
Okay? This is actual transit days. Next one, transit variance. Okay? What is that? Transit variance.
Speaker A
What is a transit variance? So, which is a difference between Okay? So, by this what we can do? We can go for making like whether it is a traveling the product is shipped faster or slower.
Speaker A
Okay? Whether it is whether the products are shipped faster or slower. That is actual transit.
Speaker A
Variance is like difference between Okay? How many days difference between the actual transit days and estimated transit days.
Speaker A
Okay? So, So, based on the estimation, what do we do? Within 5 days it should reach.
Speaker A
Okay? That is estimation. But, what is actual transit? It reached after 7 days. So, there is a difference of 2 days in between the actual transit days and deliver estimated. So, you understand the main concept here.
Speaker A
Okay? So, what is that main concept? Is we will understand so simply when we compare with uh what is that?
Speaker A
IPL. Cricket IPL. Simple. Cricket IPL. So, what is that cricket IPL? So, we see that predicted score.
Speaker A
Okay? We see that predicted score based on the runs per over. What is that? Runs per over.
Speaker A
So, what is that predicted score? Probably in within uh 20 overs it should be 200 because now the current run rate is 10 per over.
Speaker A
Okay? Current run rate is 10 per over. Here, and what is that? Actual score.
Speaker A
So, due to losing wickets, okay, they scored 180. Actual is the last score. Predicted is the target score, 200. Okay? What is the variance?
Speaker A
20 runs. What is the difference between the actual score and predicted score? So, here also, if you see, okay, estimated is the predicted score.
Speaker A
Okay, what is that? Estimated transit days are like a predicted score. It should be reached within 7 days.
Speaker A
But what is actual? Okay, for example, it should Estimated is 5 days. But actual is the day when the product is delivered. What is actual delivery date or actual transit days? The day when it is delivered. 7 days.
Speaker A
Okay? There is a difference of variance of 2 days. So, this is what we are completely discussing on the the complete concept on the logistics transportation.
Speaker A
Okay? Next one is fuel surcharge USD. Okay? So, what we are calculating here? We are calculating freight revenue multiplied by fuel surcharge percentage.
Speaker A
So, we are using a function called as a coalesce. You hear one function that has been common in other programming languages also.
Speaker A
Okay? What is coalesce? I think in the PySpark also we have the same function.
Speaker A
Coalesce is the function when it finds anything null. Okay, it can replace with any other number.
Speaker A
Okay? Any other number. So, which is used for replacing the blanks or replacing the nulls with any text or any number that we give. Okay? So, what is that? Coalesce fuel surcharge is percentage. If it is equal to zero or if
Speaker A
it is equal to null, if it is equal to blank, then replace it with a zero.
Speaker A
Okay? If it is a blank or null, replace it with a zero. Simple. And after that rounding it, rounding it to the two decimals.
Speaker A
Okay? And related is the function which is used to get the column from some other table. And again, that is based on the relation between the tables, many-to-one. It should maintain many-to-one relationship. In is the function we know that we all we also
Speaker A
have it in the uh PySpark. Okay? Whether it if it is in road rail, you make these calculations. Freight revenue multiplied by fuel surcharges.
Speaker A
Okay? We get fuel surcharge USD. Next one, total revenue USD. What is that? Freight revenue plus fuel surcharge USD. So, that is the total revenue.
Speaker A
Okay? Total revenue USD. Next one is revenue per kg. So, how much revenue that the logistics is making per kg.
Speaker A
So, what is per kg? If you do not Okay, I'm not able to understand. So, when you go to DTDC courier with the package, what does he do first thing? He put it on the weighing machine and based on the grams or based on the
Speaker A
kg, he's going to and also based on the distance. So, he's going to charge the amount. So, here also the same thing.
Speaker A
Okay? What is that? Revenue per kg is like freight revenue divide divided by the kg.
Speaker A
Okay? Freight revenue divided by the kg. So, that we get how many kgs per revenue.
Speaker A
Okay? Revenue per kg amount dollar per kg. So, this is all about the calculated columns.
Speaker A
Okay? This is all about the calculated columns. What are all the measures that we created?
Speaker A
Okay? What are all the measures that we created? Let us go one one by one.
Speaker A
So, why did we create calculated column? Because we want to see them as a separate columns.
Speaker A
Okay? Calculated column we see them as a columns. And measures, we do not see them as a columns. We can see the number. Okay? It's a single entity.
Speaker A
So, what do we What is the first thing? Average delayed days. Simple straightforward. Average is the function. It is applied on the delivery delayed days. So, we get to know for example, if you ask someone how many people are shipping tea every day in
Speaker A
your shop in a in a tea stall, so he cannot say the exact number. So, he would probably say that on an average every day 200 people visit the tea stall.
Speaker A
Or he can say the range 200 to 300. Okay, here also the same thing.
Speaker A
This is average transit. So, based on the actual transit days, average transit variance based on the transit variant days. Okay, actual I mean the main transit variance and the average.
Speaker A
The next one is delivered shipments. Okay, based on the actual delivery. So, these are all the delivered So, we use calculate function. Calculate is one of the most important DAX function in Power BI, which is used to for calculating the
Speaker A
which includes it's a dynamic function which includes the expression comma filter condition. It takes two parameters, expression comma filter condition.
Speaker A
So, we are applying fact shipments and we are filtering the data which is not blank.
Speaker A
Freight revenue. So, we are taking sum of freight revenue. Fuel share. What do we have in the fuel share percentage? So, it is based on the fuel surcharges and total revenue.
Speaker A
Okay, it is based on the fuel surcharges and total revenue. What is fuel surcharges?
Speaker A
Simple straightforward sum of fuel surcharge USD. On-time delivery. So, on-time delivery is based on the division of on-time shipments and delivered shipments. So, we will get the percentage. Okay, so we will get the percentage.
Speaker A
Next on-time with extra days. So, this is we use it in one of the visual for what if parameters.
Speaker A
Okay, we will see at the time of visualization. Okay, and on-time percentage we take the percentage. Okay, threshold percentage.
Speaker A
Planned transit, okay, estimated transit days. We are We are naming it as a planned transit. Okay? Revenue per kg, yes, we have seen that is freight revenue and the weight kg.
Speaker A
SLA breach percentage. Okay? So, it is SLA breaches and how many of them are delivered.
Speaker A
Delivered shipments. So, we get the percentage. And SLA breaches. And what is the penalty?
Speaker A
Okay? So, a carrier a carrier did not meet meet the SLAs. So, what do we do?
Speaker A
What the logistics company is going to do? It is going to impose the charges, penalty.
Speaker A
Okay? It is based on the SLA breaches. How many are the count of breaches multiplied by penalty per breach.
Speaker A
So, they have the standard penalty that has been in the carrier. So, we go for it.
Speaker A
Total revenue, sum of total revenue USD. Total shipments, count rows. It is going to count the number of records.
Speaker A
Undelivered shipment. So, simple. Total shipment minus delivery shipments. Or this can also be made like uh we can go for filtering the data. SLA status equal to not delivered. Simple.
Speaker A
Another way of doing it. Okay? Another way of doing this calculation. So, yes, these are all the calculations that we involved. Okay? That we included. So, we created another table called as a extra days table.
Speaker A
Okay? Based on the generate series. Okay? Based on the generate series, what we are trying to do? So, we are trying to generate the number in between zero to five with the step one. Which is like 0 1 2 3 4 5.
Speaker A
Okay? So, we created one selected value extra days. Why do we need to use extra days? We will see at the time of visualization.
Speaker A
Okay. So, are we good? Are we good till now? Sir, one doubt, sir. Yeah, please.
Speaker A
Okay. Sir, we can make the calculated columns, no? Yes. So, So, calculated columns are stored in the shipments.
Speaker A
Shipment table, in the fact table. Yes. Okay, sir. Thank you, sir. Yeah. Sir, extra days table is not connected to anyone.
Speaker A
Yes, they are like non-linked tables, but we can use it in the visualization and calculations.
Speaker A
Uh same like the date the date table we make right sir? Yes, date table is in relation, but uh in extra days, which is not linked, but we can use the calculation and implement in the visualization.
Speaker A
So, this of a tables are called as a non-linked tables in Power BI. Okay, sir. Thank you, sir.
Speaker A
Yeah. So, now let me click on file, okay? And let me click on new report, okay? Now, let me click on new report.
Speaker A
Okay, now you see. So, we got the online report building. So, previously what we used to do is like so, we go for Power BI Desktop and we used to build the report, implement the calculations and all.
Speaker A
Now, what do we do? We can online in the online we can directly build the report.
Speaker A
But, for our understanding, let me click on let me add something over here. Okay? Let me try downloading this report.
Speaker A
Okay, now let me save this first of all. So, it is saying that you should save this. Okay, let me save this.
Speaker A
Okay? So, let me name this as fleet OPEX read Okay? performance dashboard V2. Okay? This is just like a version two.
Speaker A
Okay? Now, let me click on file and download this. And now you see when we click on download, it is not giving the copy of the data because it is live connection.
Speaker A
Okay? Whatever the changes that we do in the semantic model, it is automatically reflected in the report.
Speaker A
Let me click on open this. Now, let us see what we are able to see in the report.
Speaker A
What we can see, what we can build in the report. Okay? So, now it is loaded.
Speaker A
So, if you see when we connect Okay? As we discussed, there are three types of modes, connectivity modes in Power BI.
Speaker A
So, what do we see here? So, we are able to see the report view and the model view.
Speaker A
Okay? What do we see here? We see we are able to see the report view and model view.
Speaker A
So, in the live connection or in the uh yeah, in the live connection, we will not be able to see the data.
Speaker A
In the direct query, we can see the table view, but we will not be able to see the data.
Speaker A
Okay? So, here we do not we will not be able to see the uh the table view also in the live connection.
Speaker A
So, let me go to the model view. Okay? And let me make some changes in the data modeling part. Okay? In the relationship.
Speaker A
Now, you see everything is faded out. Okay? So, whatever we see, everything now we are not it is not clickable.
Speaker A
Okay? So, we will not be we are able to see this, but we will not be able to edit anything in here.
Speaker A
Okay, anything we cannot change anything. Okay, now you see connected live to Power BI semantic model.
Speaker A
And [snorts] what is this? Make changes to this model. So, if I click on this, okay? So, what it is saying a direct query connection is required. To make changes to your model, like renaming columns and adding data from multiple
Speaker A
sources, you will need to switch to direct query connection. This requires adding a local model to your file, and it is a permanent change. What it is saying, if you want to make changes to this data, okay? You need to add a local
Speaker A
model lock some data into this. So, if I click on add a local model, I will get that, but I do not want to make any changes right now.
Speaker A
Okay, once we build, let us see that. Okay, connectivity modes and storage mode. What is a storage mode? Okay, so how Power BI is storing the data, simple.
Speaker A
Okay. So, let me remove this. First page is overview page. Okay, first page is overview page.
Speaker A
So, what do we have in here? Overview. Okay. So, here we when we get into Power BI, okay? So, this is report view.
Speaker A
So, we can build the reports over here. There is a default filter pane, visualization pane, and whatever the data that we pull, we can see the data over here in the data pane, p a n e.
Speaker A
Pane. Okay. So, what do we see? We see all the tables over here. And I can go to the formatting the visual. Canvas setting, I can go for canvas setting.
Speaker A
Canvas background. So, let us make it into white. Okay. And let me not go for any transparent wallpaper.
Speaker A
Make it white, okay? Now you see, we are able to see that. Okay, filter pane, no, I do not want to make any changes.
Speaker A
Okay. So, let us build the report. So, what do we need? Let us go for adding the slicers, one after the other, okay? So, these are all the standards.
Speaker A
So, from the date, let me add the year slicer. Okay, from the date, let me add year slicer.
Speaker A
Okay. So, I have added year slicer. Let me zoom this. Okay. Year slicer. Go to slicer setting. Make it drop down.
Speaker A
Okay, make it drop down. So, let me try increasing the size. Okay, values are increasing. Let me increase the size of the title. Where is the title?
Speaker A
Yes, it is increasing. Okay, now we are able to see that. So, and enable the visual border, so that there is a separation between visuals.
Speaker A
Visual after another visual. Control C and control V. So, let me make it darker a little bit.
Speaker A
Effects, visual border, make it some darker. Yes, now we are able to see that.
Speaker A
So, general effects expand visual border, make it dark. So, here let me add transport mode.
Speaker A
So, transport mode, okay, let me add. Let me take this off and transport mode.
Speaker A
Yes, we added transport mode. So, just let me make some changes, okay? Let me take off underscore and let me add it. Okay. So, we added transport mode.
Speaker A
Let me copy paste it. Control C and control V. Okay. Let me click on this. What do we see?
Speaker A
What we can add here? Let me add carrier type. Okay. And the next one, let me add freight type.
Speaker A
Okay. So, let me just move this little bit. So, we can use arrow to move this left and right.
Speaker A
Okay. Let me add one more. So, what is that? Shipment status. Okay. Let me add status.
Speaker A
Shipment status. Okay. So, let me move this little bit. Okay. So, the slicers are created.
Speaker A
Okay, let me click on save this. Okay, slicers are created. So, what do we need to implement the next thing is the KPIs. So, what do we have?
Speaker A
When you get into the business intelligence side, we have something called as a KPI.
Speaker A
What is that KPI? What are the data? Which data is is driving the project?
Speaker A
What is the main measurable column? I can simply say main measurable column measurable column or a calculation or a measure Okay, main measurable column or a measure which is driving the project.
Speaker A
So, here what do we see? We can go for shipments, total shipments. Okay, what do we have?
Speaker A
Total shipments, whatever, whichever. I think we created many many measures. Okay, all the measures are a KPIs, key performance indicators.
Speaker A
So, what is KPI? Key performance indicators. Okay, key performance indicators. So, what do we have? Whatever the measure that we created, all of them are most of them are key performance indicator.
Speaker A
So, what do I do? Let me click on the card visual. So, previously 6 months back we have a normal card visual. Now, it has been updated with a new card visual.
Speaker A
Okay, with a flash symbol. What is the difference between both of them? We also have multi-row card. So, two of them are retired and we got the new one, which is a new card visual.
Speaker A
What we can do here? We can add the value, we can add the category.
Speaker A
But, the main thing is that we have a reference label. Okay? Now, let me add total shipments into this.
Speaker A
Total shipments. How many shipments are there? 5,000 shipments. Okay, how many shipments? 5,000 shipments.
Speaker A
What we can do? We can go to the multi-row card. Okay, not in multi-row card card.
Speaker A
Shape. Okay, let me check. We We are able to see it in 5K, but I do not want to see something like that.
Speaker A
Callout, I think. Okay, let us see that uh later. So, what do we have? We have a reference label option.
Speaker A
In the reference label, what we can do? We can add anything. Okay? We can add delivered shipments.
Speaker A
Out of all, how many of them are delivered? Okay, in the reference, what we can do?
Speaker A
We can add how many delivered shipments. Let me add delivered shipments. Okay? So, delivered shipments are 3K.
Speaker A
So, let me go for I think here we can change it. Delivered shipments uh value I can go for changing it into display unit none.
Speaker A
Now, you see 28573 is the shipments. Okay? So, we can also type something called as a display unit.
Speaker A
Oh, sorry. None. Yes, display unit. Uh let me check. I think here we can check. Can we check?
Speaker A
Total shipment call out value. Card total shipment adding order action Okay, let us ignore now. Okay, now you see total shipments and delivered shipments. I think it should be 5,300 something some of the value. Okay.
Speaker A
Control C and let me go for the next one. Okay, what is that? So, the next one is on-time percentage.
Speaker A
Okay. On-time percentage. So, let me take this off. Click on this. Now, we are able to see it in the value, but we want to change it into the percentage. Actually, it should be changed in the semantic model itself.
Speaker A
But, what we can do, we can go to general. So, here we will not be getting any any information. Everything is blank. You can't change anything in here.
Speaker A
If you want to change it, you need to change we need to make it into a import mode. We need to change the storage mode.
Speaker A
Go for go to data format uh in the general and click on percentage. Now, we will see 23. some value some percentage. This is on-time percentage.
Speaker A
Control C control V. So, let us add another one. Okay, which is SLA breach.
Speaker A
SLA breach percentage. Okay. So, let me also go to general. Date format, make the format into the percentage. 53 53.44 If I go for thousand separator, for example, if I go for only single decimal, now it is 54 percentage.
Speaker A
Okay. We can also add in the in the visual, we can also add a reference label.
Speaker A
So, if you want to add the reference label as SLA breach, the number I can add that.
Speaker A
So, 2,000 are the SLA breach. Okay? So, let me SLA breach make it into value none.
Speaker A
1,550. I think you are able to see. Let me zoom this. Okay? 15 51 are the SLA breach.
Speaker A
Okay? So, let me go for control C again, control V. So, what we can do here we can add another one which is average delay days.
Speaker A
What is that? Average delay days. So, how many days are on an average delayed?
Speaker A
Okay, how many days are on an average it is delayed? Okay? And the next one is control C, control V.
Speaker A
Next one is total revenue. Okay? Total revenue. Total revenue probably is the main KPI throughout the project.
Speaker A
Okay? Let me decrease the size a little bit so that we can also accommodate other visuals, other KPIs.
Speaker A
Okay. So, let us have uh two more KPIs. Okay, let me move this a little bit.
Speaker A
Just let me add Okay, let me take this off. Okay. Let us only have the last one.
Speaker A
So, what is that the next one is SLA penalty? So, how much penalty that the the third-party vendor is paying?
Speaker A
Okay, this is a total penalty, $6 million. Okay, $6 million is the total penalty.
Speaker A
So, later on we can arrange those visuals okay? So, if I go for decreasing that, I think we have a little bit of So, I can also go for undelivered shipments, okay? I can also go get it.
Speaker A
Let me decrease the size and let me put it over here. Okay. Do we have that? Let me check.
Speaker A
Undelivered shipments. Okay, 2,000 are undelivered shipments. Okay. Now, let us see the next one.
Speaker A
Let me click on save this. So, what we can do here? Okay. So, we have something called as a line chart.
Speaker A
Okay, we have something called as a line chart. What is this line chart is what we can do with the line chart is Okay, so if you see if you see the same thing from the cricket Okay, if you see same thing from the
Speaker A
cricket. So, what do we see in the line chart? Or if you see the last couple of years stocks trading, okay? It goes up and down, up and down, up and down, okay? It goes something like this.
Speaker A
Okay, so this is the year and this is the price. Or this is the year and price or this is the year, this is the price.
Speaker A
Now you see here 2000 from 2020 so what is the price? Till 2026. I still remember the gold price was like in the in the last 2 years. So it was around 84,000.
Speaker A
Okay? And now it is 1.4 lakhs, I think. Okay? Now you understand how it is increasing and decreasing. So we use stocks Nifty, okay? Whichever the uh visual, okay? Whichever the data that needs the monthly or daily or weekly, okay?
Speaker A
Trend analysis. For the trend analysis, we go for using line chart. So what do we do in this line chart? So we can go for year month.
Speaker A
Okay, I can go for year month in the x-axis. Okay? And on time percentage in the y-axis.
Speaker A
I can directly check out what is my on time percentage. Okay? So this is my on time percentage.
Speaker A
Okay, we here let me take this off. Now you see on time percentage. Let me click on focus mode.
Speaker A
So what do we see every Uh let me increase the size. Okay, now you see from 202707 so we have the data. I think it is not uh let me click on three dots. Sort this visual.
Speaker A
Okay, don't we have sorting? Sort by year month. Okay, now we are able to see the data by the new, I mean, the latest data till the from 2026 till 2023.
Speaker A
Okay, 2023. So what do I do? I go to the format your visual. I go to the zoom slider. I can have what is the line?
Speaker A
Okay, if I want to change, what is the line style? If you want dash, yes, you can make it dash. Dot, yes, you can make. Let me go for solid only. So what is the interpolation? Now it is smooth.
Speaker A
If you want step, yes, you can go for linear. Step, yes, you can have the step, but let us go with the linear. Sorry, smooth.
Speaker A
Okay? Color, if you want to make some changes to the color, let me go for changing any color.
Speaker A
Okay? Layout, no. Shade area, if you want, yes, you can have the shade area.
Speaker A
Marker, let me have the marker. Data labels, let me enable the data labels, so that we get some information on this.
Speaker A
So, what do you see here? If I hover over the first 2026, okay? The on-time percentage is 19%.
Speaker A
Only 19%. Let me go to general, data format, on-time, let me make it into the percentage.
Speaker A
In the year 2025 05 07, okay? July, I think in the month of July, it reached to 45%.
Speaker A
Okay? On-time delivery percentage is only It is not even reached to 50%. As a data analyst, what do you need to analyze the first thing?
Speaker A
Okay? After observing from the historical data, from 2023 1st January till 2026 9th month, I'm able to see that it did not even reach On-time means with within the delivery, did it reach or not? It did not even cross 50%.
Speaker A
Okay, you see? It did not even cross 50%. This is our first analysis. On-time percentage, it did not reach 50%.
Speaker A
Okay, this is our first analysis. What is the next one? Okay? What is the next one? So, let me go to the general, effects, visual border, let me enable the visual border. Yes, it is enabled. Let me make
Speaker A
it a little darker. Okay? Next one is Let me go to The next one is total shipments by SLA.
Speaker A
Okay? So, let me go for taking the clustered bar chart. Okay? Clustered bar chart.
Speaker A
So, what I can do here? I can go for having SLA status. Okay, let me add on-time percentage. Okay, not on-time percentage.
Speaker A
SLA status. Okay? And the next one is total shipments. Total shipments. So, what do you see?
Speaker A
Most of them are breached. 1,551 are breached. Okay? And the next one is 1,127 are breached. Not delivered. It is not delivered. In transit, 689 are in transit. Not yet delivered.
Speaker A
Next one, 685 are on time. 669. 637. You see You see 689 out of 5,330 shipments, 689. Probably like 20% I think. If you go for analysis part, only 20% are delivered on time.
Speaker A
Okay? So, if you want to know, you can go for making into donut chart. You will get it.
Speaker A
Okay? In the donut chart, if you see out of 100%, what do you see?
Speaker A
Uh what is that? On time. Okay? Not even 12% only. Okay, you see? 12% are delivered on time.
Speaker A
Okay? 12% out of 100%, 12% are delivered on time. Let me take this off.
Speaker A
Okay? So, this is Uh let me go for making into the same thing. Okay? I can go to format your visual, enable the data labels.
Speaker A
Uh go to the bars and change a color a little bit. Okay? Yes. Next one.
Speaker A
Okay, let me just uh decrease the size a little bit. Okay? The next one is total shipments by year month.
Speaker A
Okay, let me take a column chart clustered column chart and here let me add total shipments.
Speaker A
Okay, and let me add year month. Year month into the x-axis. Let me click on focus mode.
Speaker A
So, how many are the total shipments? Okay. How many it is increasing? If you see, okay, it is not Let me make it into sort by year month.
Speaker A
Okay, this is decreasing. The shipments are decreasing. Okay, the shipments are decreasing. Let me go back and let me change the color.
Speaker A
Column columns go to columns and change the color into good color. Okay, a little bit.
Speaker A
Okay, next one is total revenue by transport mode. Okay, total revenue by transport mode.
Speaker A
So, I can go for taking clustered bar chart. Total revenue by transport mode. Total revenue by transport mode. Let me go for mode and let me click on this.
Speaker A
Okay, total revenue by transport mode. So, if you see here air which is making more amount.
Speaker A
75 and 43. Air mode is the best way of making more amount total revenue.
Speaker A
Okay, I can go here. Bars change the color to Okay, this color or let me go for changing into another color. Yes, now you see, now it looks good.
Speaker A
Let me click on save this. And decrease the size. Okay, now we are able to see this. Okay. Now the report is most of the things are being Okay, first overview page is completed.
Speaker A
Okay, first overview page is completed. So, they would say that we need to build the interactive dashboard.
Speaker A
Okay, they would say that. Build the interactive dashboard. What is interactive dashboard? So, if you make if you click on anything, the other visual should also refresh or get filtered.
Speaker A
So, let me click on year 23. Now, you see it is getting filtered. 24, it is getting filtered. 25, it is getting filtered. So, that is interactive dashboard. Or if I click on any one of the visual cascading of the
Speaker A
filters. If I clicked on breached, now you see 100% 2K shipments are breached. Average delay days are 4.79.
Speaker A
Probably it can we can go for uh uh uh uh uh uh five days.
Speaker A
Total revenue 71.80 million dollars only on that breached shipments. Okay. Not delivered. Now, you see they need to focus more on these not delivered. 51.55 million dollars are not delivered. 1,000 shipments are not delivered.
Speaker A
See, that's a big problem to the to the logistics. Okay, these are not delivered. And out of this, transport mode air is not delivered. It is contributing more. 2.74 uh million dollars.
Speaker A
Okay. And the next one is some value 1. 61. And the 79. Okay, some value.
Speaker A
Okay, this is interactive dashboard. So, let me end the session. Let So, the first page is overview.
Speaker A
So, this is a standard format of building the report. So, in the overview page, I can come here.
Speaker A
And I can go to So, I can easily find out what my data is speaking about. So, I know what is that? The number of shipments.
Speaker A
So, what is the on-time percentage? So, it did not even reach 50%. So, this is what we can explain in the interview. Uh this is what we can explain uh the same thing to the the client or to the stakeholders
Speaker A
logistics organizer or logistics manager. Okay, freight operations manager. So what is the SLA breach? 54% so out of 5300 shipments 1551 are the SLA breaches. Okay, these are the SLA breach number.
Speaker A
So what is the average delay? And what is the total revenue? 247.25 million dollars or what is that rupees?
Speaker A
You can say whether it is in a dollar or in the rupees. You can say that.
Speaker A
And what is the what is the penalty? 6 million. So if you see USD if the penalty is in USD the total revenue should not be in the Indian currency. So it is 247 million dollars is the total
Speaker A
revenue out of that 6 million dollars is the penalty. And what is undelivered shipments are 2000 change undelivered shipments.
Speaker A
So they have shipped but not delivered. So this is on time percentage trend analysis total shipment by the status and total shipments by the year month total ship total revenue by the transportation mode.
Speaker A
So this is in the overview page. Now let us go into the second page. But what I do what I recommend so we are maintaining one template in this page throughout the report. So what I what I recommend is like
Speaker A
so maintain the same thing in other pages also. So whatever we maintain the template or the background color of the visuals in the first page so we should be maintaining the same thing throughout all the pages. So right click and click
Speaker A
on the duplicate. So when we click on the duplicate we get the template. So when I clicked on the page one when I selected when I clicked on the plus symbol for another page so it is giving this kind of a template. So
Speaker A
gray color background or wallpaper. So let me go to the duplicate overview and let me rename this as a carrier performance.
Speaker A
So this page would completely speak about or give the insights about the carrier performance.
Speaker A
So let me take this off. All the cards. Delete this. So the next step is The next one is So let us select the carrier name and on-time percentage.
Speaker A
Okay, let me take this off and let me select expand the visualization and select clustered bar chart.
Speaker A
Okay, select clustered bar chart. So what do we need to put it over here is carrier type. Okay, carrier name. Let me scroll up.
Speaker A
Carrier name. Okay, and the next one is on-time percentage. On-time percentage on the x-axis.
Speaker A
Okay. On-time percentage. So we also have something called as a tool tip. So what is a tool tip? For example, if I hover over it, so what I'm I if I hover over the bar, okay, so I'm able to see the carrier name, Verizon
Speaker A
carriers, and what is the on-time percentage? It is 0.3435 is the on-time percentage. I also want to see what is on-time percentage target and also you what is the revenue associated or what is the gap in between these two.
Speaker A
On-time percentage and what versus target. So how do I do that? So in the tool tip, I can add carrier tire.
Speaker A
Okay, so but I let me add on-time on-time percentage target. Okay, on-time percentage target.
Speaker A
Next one, gap. And next one, carrier tire. Okay, what is the carrier tire? Now let me Let me click on the focus mode and let me hover over it. Now, what do we see here?
Speaker A
The carrier name is Horizon carrier. And on-time percentage is 35%. We need to convert that. Let me click on this, go to general. Act- actually, this need to be converted in the uh data semantic model only, but let me
Speaker A
do it in the report. So, click on the percentage. Okay, click on the percentage. Now, let me hover over the bar. Now, you see? So, what do we see here? Okay, let me also change the on-time percentage target to the percentage, also.
Speaker A
If you If you If you hover over it, on-time percentage is 35%. What is the carrier tier?
Speaker A
Tier two. But, the target is 95% 90%. What is the gap between the actual and target is 55%.
Speaker A
Okay 55%. So, I can hover over it, and I can see that. The user or the client can hover over it, and he can see that.
Speaker A
Okay? So, this is So, this is about the on-time percentage. Okay, this is about on-time percentage.
Speaker A
Let me see if there are any No, nothing. Okay. All right. Now, what do we need to do? Now, the next step. So, for example, if I want to go for giving the giving the color coding.
Speaker A
Okay? So, if I want to go for giving color coding to the bars. Okay? So, let me go to format visual.
Speaker A
So, I do not want to go for individual color, but I want to go for the conditional formatting. Let me click on the effects.
Speaker A
So, we have something called as a mode. Okay, mode color. Okay? So, mode color. Let me go for field value.
Speaker A
And click on okay. So, based on the mode, probably that mode it represents the transportation mode.
Speaker A
Okay, now you see, we are able to see the color. Okay, what is this color belong to? Okay, red color, green color, and blue color. So, what do we have in the mode?
Speaker A
Okay, what do we have in the mode color? So, if you want to see that, we can see it in the semantic model.
Speaker A
Okay, we have the color coding for the air airways roadways railways. Okay, this is the color coding.
Speaker A
Let me click on back to report. Okay, we also added the colors. So, what do we need to do the next step?
Speaker A
The next step is penalty. Okay, so what is the carrier name? How much penalty are they paying?
Speaker A
Okay, carrier name and the penalty. So, what I can do here, so let me go to the format build visual. Let me take this off.
Speaker A
What is that carrier name? Okay, let me add carrier name to the Y-axis. And what is the next step?
Speaker A
Penalty. Okay, SLA penalty. Okay, SLA penalty. So, now what do we see here? Let me click on the focus mode.
Speaker A
So, what do we see? Pacific some name a large. So, what is that? It is 0.37 million dollars is the penalty that they want to go for it.
Speaker A
Okay, and Titan Express so 0.37. And if I go for this, Crest Transport is 0.03 million dollars.
Speaker A
Okay, this is the penalty that they are paying. So, this is based on the carrier.
Speaker A
Okay, this is based on the carrier. For example, if I want to add the legend, okay? Add the legend. What is that legend? Segregation of the data. I want to add acquired by.
Speaker A
Okay, who are all acquired by? Let me click on legends. Okay, now what do I do? I get Okay, okay, let me let me make it into clustered column chart. I'm not clustered column chart.
Speaker A
Okay, it is not working. Okay, let me take this off. Okay, so let me go for Let me click on this. Now you see, we are able to see the penalty by carrier name.
Speaker A
Okay, this is penalty by carrier name. So, the next step is carrier tire versus SLA breach.
Speaker A
Okay? So, let me go for taking this. This would be So, carrier tire. So, let me add uh carrier tire versus SLA breach percentage.
Speaker A
SLA breach percentage. So, what is this? Let me click on focus mode. The carrier one has the SLA breach 65%.
Speaker A
I can go to general. And in the data format, I can go for changing the changing this into percentage.
Speaker A
Okay, what is this? 64.5%. Carrier 3 has 39% 40%. Okay, this is the SLA breach percentage.
Speaker A
And in the tool tip, I can we can also add Okay, in the tool tip, we can also add if you want to see more details.
Speaker A
This carrier, what is the on-time percentage? Okay? And what is the SLA SLA USD Okay, penalty.
Speaker A
Now, I can click on the focus mode. I can click hover over it. I can see what is this? On-time percentage is 24.
Speaker A
Only 24%. And what is the penalty? Now, you see 3. 98 million or 3 lakh 98,000. I think probably it is 3.9 million dollars.
Speaker A
Okay? So, I can go here. I can go to general. On-time percentage, I can go for making it into percentage.
Speaker A
Okay? Now, let me go back to the report. Okay. So, yes, we have shown that So, this is regarding the SLA. This is regarding the carrier performance. Okay?
Speaker A
What we can show more on this? So, I can show something in a table format.
Speaker A
Okay, let me show something in the table format. I can click on the arrows.
Speaker A
Okay, I can click on the arrows. And I can increase and decrease the size. Now, you see now I can drag this, and there is a dotted red line, so which is giving us the alignment. Yes, this visual is
Speaker A
aligned to other visual which is next to it. Okay? I can increase or I can decrease, okay?
Speaker A
So, based on the pixel. Okay, based on the pixel. So, let me go for taking the table visual.
Speaker A
So, let me go for taking the table visual. So, here what we can add we can add the number, okay, we can add the descriptive information, and also about the on-time percentage, what is the SLA breach, what is the total
Speaker A
revenue. So, let us start with carrier carrier name. Okay? And the next one is carrier tire.
Speaker A
What is the next one? Transport mode. And the next one is how many shipments?
Speaker A
Total shipments. And in that total shipments, how many of them are delivered? Delivered shipment.
Speaker A
In that, how many are on time? On-time percentage. On-time percentage target. So, where is the target?
Speaker A
On-time percentage target. Yes. On-time percentage target. And what is the gap in between on-time percentage and uh target.
Speaker A
And the next one is SLA SLA breaches. How many number? This is the number. Okay? And percentage. The next one is a percentage.
Speaker A
And what is the next one? Average delay days. Average delay days. And the average transit.
Speaker A
Okay, average transit days. And we can also go for average variance. Next one is SLA SLA penalty USD.
Speaker A
And the last one is total revenue. Okay? Last one is total revenue. Let me increase the size a little bit.
Speaker A
So, we have global font size. Let me increase the size. Yes. So, we have increased the size. I think we need to change the style.
Speaker A
Uh let me go for changing the style. Okay, let it be the same thing. Okay, let it be the same thing.
Speaker A
So, I can go I can go to visual I can go to general date format data format. So, let me change this into the percentage.
Speaker A
Okay. Now, we have changed it into the percentage. So, what we can do more?
Speaker A
So, let me go to the column headers and change the column header color. Okay, I can go and change the column header colors.
Speaker A
Okay, I can change the column header colors. Yes. So, let me click on center alignment.
Speaker A
Text color, let me make it into white. Is that visible? Let me click on focus mode.
Speaker A
So, now what do we see here? So, carrier name What is the carrier tire?
Speaker A
What is the transport mode? Okay, 187 shipments are total shipments. Out of that 92 only are delivered.
Speaker A
So, 90 90 180, okay? Half 50% of them are delivered. On-time is 0 3 3.
Speaker A
On-time target is 95% okay? Let me go for changing the on-time percentage into percentage. 32% is the on-time.
Speaker A
Target is 95%. So, there is a difference about 60% difference, okay? 62% is different. The target The gap is 62% gap.
Speaker A
SLA breaches, 50 shipments are SLA breached. And percentage is 54% What is average delay days? Two days is the average. Average transit I mean which is traveling from source to destination is 4 and 1/2 days. And what is the variance?
Speaker A
Okay? 2.03. And the next one is what is the penalty that is being on that carrier is 2.5 probably 2.5 lakhs. Once, tens, hundreds, thousand, 10,000, lakh. Okay?
Speaker A
2 250,000 dollars. And what is the total revenue? Okay? Some value. Actually, we need to change this the currency into the dollar.
Speaker A
So, that can be done in the semantic model only again. Okay, let's see if we can change at the end.
Speaker A
Okay? Now, let me click on back to report. Now, you see. So, the client can easily understand where we are lagging.
Speaker A
Okay? Where we are lagging. What is the carrier performance? So, what we can understand even if I if I click on even if I click on on-time percentage to ascending order or descending order.
Speaker A
Okay? Let me go for ascending order. What is the 35% is the highest on-time percentage. And that is the transportation mode is railways.
Speaker A
And what is the carrier? Horizon carrier. So, what we can what this logistics team is going to do the next step is they are going to contact with connect with the carrier operational team.
Speaker A
And they show all these to them. So, this is what you are lagging in.
Speaker A
So, can you please change? If it doesn't work, then they are going to suspend the license or the agreement.
Speaker A
So, that is what they are going to do from this page. Next one. Right click and duplicate so that we can have the same format.
Speaker A
Next one is delivery and SLA. Okay? Next one is delivery and SLA. So, let me let me remove this.
Speaker A
Let me take this off. And let me go for making it into the 100% stacked bar chart.
Speaker A
Okay, 100% stacked bar chart. So, let me take all this. Okay? So, what we can do here? So, let me add carrier tire.
Speaker A
Carrier tire. And after that, let me add total shipments. Total shipments. And after that, let me add SLA status.
Speaker A
SLA status into the legend. Okay, SLA status into the legend. So, go to formatting visual.
Speaker A
And the data labels, let me expand the data labels. And now, let me go to the bars.
Speaker A
And we can we can change the color coding. Okay? And here, we need to go for filtering the data. SLA status, we do not want all to be shown.
Speaker A
What we can do here, we can ignore on time. Okay, we can ignore on time. And also, we can ignore in transit. And in transit uh delayed. So, our main focus is on the breached, late, within SLA, and
Speaker A
and the next one is on time. Okay, next one is on time. Or what we can do, we can also go for not delivered.
Speaker A
Okay, not delivered. So, on time, in transit, in transit delayed, we are not worried about it because they are in progress.
Speaker A
So, we are mainly focusing on the breached, how many of them are breached, late, within SLA, and not delivered.
Speaker A
So, what we can do here, so I can click on focus mode. Okay? Now, let me go and change the color. Breach.
Speaker A
And let me go for changing the color coding so that it looks good. Light colors. So, every time you think that light colors are preferable.
Speaker A
Okay? Now, you see it looks good. What we are able to understand from here?
Speaker A
So, carrier tire Okay, carrier tire one. And it is breached and total shipments are 780 796.
Speaker A
In the tire one Okay, 796 138 and 434 83 are not delivered. Okay, breached, late, and not delivered.
Speaker A
So, they can understand easily. Okay, they can understand easily. So, this is total shipments by carrier tire and SLA.
Speaker A
So, what is the next one? Okay, what is the next one? The next one is what we can go is what is the route region and on-time percentage.
Speaker A
Okay, next level is like route region. Let me go for route region. Where is that? Okay, let me go here.
Speaker A
Route region and on-time percentage. So, the next level of drill down on-time percentage. Okay, so what we can do in the tool tip? We can add SLA SLA breach SLA breach percentage and the next one is delivered shipments.
Speaker A
Okay, delivered shipment. So, let me go for focus mode. So, what I can do here? North East Central. There are five regions over here.
Speaker A
For this North region 0.26 26% is the on-time delivery. Okay, let me go for again.
Speaker A
Uh data format on-time go for percentage. Okay, so 25% is the on-time. SLA breach is 52%.
Speaker A
SLA breach is 52%. And delivered shipments are 649 delivered shipments. So, North region comparing with other regions, it is faster.
Speaker A
Okay? It is 25%. Only quarter is on-time percentage. 52% is SLA breached. And delivered shipments are 649.
Speaker A
So, here we can also add total shipments. So, if you also want to see total shipments, yes, we can also add total shipments in the tool tip.
Speaker A
Total shipments. I can hover over here. Out of 1,244, 649 are delivered. Probably, I can simply say 45%. 45 to 55 50% are only delivered. Okay, 650 650 1,300. It is 1,244. So, 45% is only delivered. 55% is
Speaker A
not delivered. Okay, this is for the region wise. Next one is sector. Okay, we can go for by sector.
Speaker A
By sector, how much is on-time percentage? Okay, how much is on-time percentage? And let me take this off.
Speaker A
So, I can go for uh SLA breach percentage and delivered shipment. SLA breach percentage and delivered shipments.
Speaker A
So, this is by sector. Okay? So, if you want to go for changing the color, yes, I can go. But, before that, data format. Make it yes, it is percentage. On-time, yes, it is percentage. Okay, that's right.
Speaker A
[sighs] So, color coding, I can go to bars. For all, let us change it into this color. Okay.
Speaker A
Uh what do we need to do? We need to enable the data labels so that we can see the numbers.
Speaker A
For the sector retail, okay? 28.83% on-time percentage. SLA breaches is 53% and delivered shipment are 274.
Speaker A
Okay, retail is standing on the top most position. And the last one is automotive.
Speaker A
Okay, only 20% has been on time. Okay, I mean why why this? Because automotive for example you are carrying heavy products. Okay, retail is like or food and beverages is like lightweight.
Speaker A
Okay, only you are sending some processed food or probably like you are selling you are selling the snacks or beverages. So that can be lower weight.
Speaker A
Okay, that can be lower weight. If you go for electronics or constructions, manufacturing this can be heavyweight.
Speaker A
Automotive this can be heavyweight. If they are carrying the heavy machines, so it takes a good amount of time to reach the destination.
Speaker A
That can also one of the possibility. Okay. Now let me delete this. So if you want to have we can have it.
Speaker A
Okay, we if you want to have we can have it. Uh let me decrease the size a bit.
Speaker A
Okay. So here it is a little tricky thing. Okay, what we can do here? So let me take the same thing. Let me go for taking a clustered bar chart.
Speaker A
And let me add something over here. What is that? Total shipments and freight revenue.
Speaker A
Okay. So let me go for adding shipment status. Shipment status. And in the X-axis let me add total shipments.
Speaker A
Okay, total shipments. So in the tooltip let me add freight revenue. In the tooltip let me add freight revenue.
Speaker A
Okay. So I can hover over it. Shipment status is delivered. 2008 total shipments and the freight revenue is this amount. Okay. This is good. Let me go for changing the color coding bar color. Let me change it into
Speaker A
this color. Okay, good. Or I can also go for changing into something else. Okay, good.
Speaker A
So, the next thing, okay? The next thing is on the what if condition. Okay, based on the parameters.
Speaker A
What is that parameters? So, the parameter is like for example, Okay, simple simple example. Let us take our regular layman example.
Speaker A
So, what is that example is, okay? You took debt from your friend. $1,000 you took from your friend.
Speaker A
Okay? So, what did you promise? You promised that you are going to You promised that you are going to pay within 20 days.
Speaker A
Okay? Your salary is going to credit, so you are going to pay within 20 days.
Speaker A
But the thing is that you did not pay. By 30 days also, you did not pay.
Speaker A
Okay? It's been 30 days, you did not pay. Your friend is calling and you are asking money.
Speaker A
So, what he's saying, your friend what your friend is saying that you said that you are going to pay within 20 days, but it's been 30 days.
Speaker A
It's been 30 days. But on 35th day, you paid amount. 35th day, you paid amount.
Speaker A
Okay? You paid amount. You thought something. To my friend, I should have said 35 days. 35 to 35 to 40 days.
Speaker A
So, I did a mistake. I did not judge. Okay? So, I did not understand. Okay, I did not understand that. I did not predict that I'm going to pay within 35 to 40 days, but I promised to pay within
Speaker A
20 days. Okay? So, here also the same thing. For example, if the carrier said based on the what if condition, okay? How many extra days that we can go for here?
Speaker A
Okay, how many extra days we can go for here? So here let me add the extra days as a slicer.
Speaker A
Where is the slicer? So here in the slicer let me add extra days. Extra days into the slicer.
Speaker A
Okay, I can add one and click on enter. It changes, okay? Here this is for slicing the data. So if you let me go here slicer header slider I can enable or disable.
Speaker A
Selected range track, okay? Let it be, okay? Let me click on Okay, let me increase the size.
Speaker A
Okay. This is the slicer. What do we have in the slicer? So we have from zero to five days extra days.
Speaker A
Okay, zero to five days. Okay. So what I I can add here is I can go for taking a card visual. Okay, let me copy from the first one. First page.
Speaker A
Control C and let me add it over here. So you will understand with the output.
Speaker A
Okay, you will understand with the output. Let me increase the size. So let me take this off. I think uh it is not necessarily needed. Let me take this off.
Speaker A
So here what I can add here is I can add on time percentage. Okay, I can add on time percentage with extra days, I think.
Speaker A
Yeah, on time percentage with extra days in the value. Okay, so let me go to let me go here.
Speaker A
Oh, sorry. On time percentage. Okay, let me add Yeah, on time percentage with extra value.
Speaker A
Let me go to format visual. Uh go to reference label and we can also add some extra days, okay? In the reference label I can also add on time percentage.
Speaker A
On time percentage into the reference label. Next one is on time percentage with extra days.
Speaker A
On time percentage on time percentage with extra days, okay. Okay, that's right. And how many of them are delivered shipments?
Speaker A
Okay, delivered shipments. Okay. All right. So, what do we see? Let me Let me click up Let me increase the size.
Speaker A
Okay, I increase the size. So, do we have anything like focus? No, we do not have it.
Speaker A
So, what is this extra days? Okay, extra days, let me pull it down. Increase the size. I think font Yeah.
Speaker A
So, now we are able to see this. Okay? So, let me go for zero. First of all, let us go with the zero.
Speaker A
Okay? So, what do we see here? In here, the on time percentage is 24%.
Speaker A
Okay? On time percentage with extra days is 23.84%. That is the general percentage that we added.
Speaker A
Okay, that is the general That is the standard on time percentage days. Okay? So, let me also add another visual.
Speaker A
Okay, clustered column chart. Let me go for adding. Uh where is that? Clustered column chart.
Speaker A
So, here I can add what I can add here is extra days. Extra days into the X axis into the X axis on time percentage with extra days.
Speaker A
On time percentage with extra days in the Y axis. Okay, on time percentage with extra. So, let me go for making it into the percentage again.
Speaker A
percentage Okay, with a zero 23.84 is the actual percentage within the data. 23.83 is the actual percentage.
Speaker A
If I go for one Okay, if I hover over to one Okay, what [snorts] is the on time percentage? 35 days. So, we promised to deliver within four days. For example, we promised to deliver within five days.
Speaker A
Okay, it is 35.85 percentage. For example, if we promised to deliver within extra two days Okay, on time percentage is probably 50 percentage.
Speaker A
So, if you want Okay, I do not want this extra days to be this slicer to be affecting my uh bar chart, what I can do I can I can expand this. We have something called as a uh edit interaction.
Speaker A
Yeah, edit interaction. So, let me click on edit interaction. What I can do I can disable the filter on this visual. Okay, that's wonderful.
Speaker A
Okay. That's wonderful. Let me go to the format again. Let me take this off.
Speaker A
Okay, so what you're able to see? Okay, what you're able to see? So, if I go for zero days Okay. zero days So, what is this zero days? Okay, this is 23.84 percentage.
Speaker A
For one day, extra one day, 35.85 percentage. Okay, so let me enable the for this let me enable the data labels.
Speaker A
Okay, let me click on focus mode. Now, you will understand easily. So, let me change the color also.
Speaker A
Okay, let me change the color. Okay. Now what we see. Okay. Now what we see. Let me increase the font size.
Speaker A
I think it can be the x-axis. Okay. Uh subtitle. Okay, that can be the data labels.
Speaker A
Yeah. So 87. Okay, now you need to understand the same concept over here. So we followed the standard uh SLS agreement.
Speaker A
You said that you're going to deliver within promised timeline. Okay, 5 days. For example, you said 5 days.
Speaker A
Okay. For example, extra days. 5 + 6 days. You said that you're going to deliver within 6 days.
Speaker A
Okay. What is on-time percentage? 35.85%. You promised to deliver within 5 days. Okay, actually this is the value of 5 days.
Speaker A
Okay. So this is the value of 5 days. Okay. Actually, this is the value of 5 days.
Speaker A
Standard 5 days. You said that you're going to deliver within 5 days. 23.84% is the on-time percentage. For example, you said that you're going to deliver within 6 days.
Speaker A
35.85%. You said 7 days. I'm adding one one one one day. I'm adding plus one day. Plus two days. Okay. I'm adding plus one day.
Speaker A
And again plus one day. I'm adding another one day. So what is the what if condition?
Speaker A
I promised to deliver eight days within eight days. Okay, 62.65%. I promised to deliver 9 days, 75.15%.
Speaker A
I promised to deliver 10 days, 87.47. This 87.47 is also not the industry standards.
Speaker A
Okay, the industry's threshold is 90 90%. Okay, you should be delivering the product Okay, within the given time. 90% is the standard threshold.
Speaker A
Standard threshold point. Okay. So, what we are able to see? So, if you increase the days, Okay, the the on-time percentage is getting delivered. So, this is what-if parameters, what-if condition.
Speaker A
So, what plus point that you can see? So, you can also this is an add-on when you explain in the interview.
Speaker A
So, the extra days is also calculated. The promised day, so on-time percentage with extra days, that visual is also given.
Speaker A
Okay, in the card, so we created I created the extra day slicer, and on the card, the on-time percentage is given.
Speaker A
So, in the slicer, we have the days added, 1 to 5 days, and within by 5 days, it reached to 90%.
Speaker A
That's a beneficial for the logistics operations team. So, this is about the delivery. Why I suddenly received Okay, it is plugged in. Okay, no problem.
Speaker A
Okay. So, let me rename this as delivery and SLA. Okay, delivery and SLA. Okay wonderful.
Speaker A
So, let us go to the next page. Okay, what is that next page? What do we have in here?
Speaker A
So, we have routes. Okay, what do we have? We have route. So, this page completely belong to the route.
Speaker A
So, we would be explaining about the routes. Okay? So, let me go for right click and duplicate, so that we can we can have the same color coding.
Speaker A
Okay? So, what I can rename this as routes. And the next one, let me take this off.
Speaker A
page Okay. So, what we are trying to show in this routes? Okay? So, we would be showing two visuals. Let me take this off.
Speaker A
Okay, let me delete this. So, what I can show here? So, I can show the route ID.
Speaker A
Okay, let me take off let me take the scatter plot. Okay, let me take scatter plot.
Speaker A
So, here in the scatter plot, what we can add is route ID. Okay? route ID into the values.
Speaker A
Okay, let me in the x-axis add the planned planned transit. Okay, planned transit days.
Speaker A
Next, what do we have? Average transit days into the into the y-axis. So, average transit days into the y-axis. Okay.
Speaker A
And the next one is the size. So, let me add delivered as the size. Delivered shipment as the size. The bubble size is going to increase.
Speaker A
Okay? In the tool tip, we can add lane lane name. What is the lane name?
Speaker A
Okay? And the next one is on-time percentage. on-time percentage On-time percentage. Okay, on-time percentage.
Speaker A
So, here for example, if I go for clicking on the 2026, for example. Okay? Let me go for changing the color coding.
Speaker A
Um marker, let me check in the marker. Color to this color. Let me click on focus mode.
Speaker A
So, if I hover over it, what I'm able to see? This is the route ID.
Speaker A
Planned transit is 1 day. So, this is it should be delivered within 1 day.
Speaker A
Average transit days is 10 days. Delivered shipment to two. And what is the lane? From to source to destination, Dallas to Las Vegas. And on-time percentage is zero.
Speaker A
Nothing. Okay? So, let me go for uh date, let me take on-time percentage to percentage.
Speaker A
Let me go to bigger bubble. Okay? Bigger bubble represents the delivery size. Okay? Route ID and planned is 5 days.
Speaker A
Transit days is 8 days. And it Planned transit means how many days from source to destination it should travel.
Speaker A
Okay? From shipping address to delivery address, how many days it should travel. But average is 8 days, which is 3 days more than the planned.
Speaker A
And shipment delivered is six. What is the lane type? Chicago to Detroit. And on-time percentage is 33.33%.
Speaker A
Okay? 33.33%. So, here uh we can also do one thing. Okay? I have another plan. What you can do is you can split the column.
Speaker A
Okay, what do we see here? Uh lane lane. Okay, what is the lane name that we have here? So, from and to.
Speaker A
Okay, we have something called as a from and to. Let me increase the size. So, if you would like to practice, you can practice it.
Speaker A
Okay, we have from and to. What do you need to do? You need to split this column into two columns, source and destination.
Speaker A
Okay, origin city and destination city. You can just like write something like that. What do you do? You take a map map visual.
Speaker A
So, you can go for taking a map shape map, for example. Okay, in the shape map, what you can do?
Speaker A
You can add the location. Okay, you can add the location. I think here we do not have, but if you go for map, you can get you can get the lines, okay?
Speaker A
So, I can click on I think let us not deviate. Okay, you will get the line.
Speaker A
Okay, how do you get the line? So, there is a there would be a chart. So, what is that? From and source source to destination.
Speaker A
Okay, so you get the line mapping. So, how the product is traveling? You get the mapping, source and destination mapping.
Speaker A
Source city origin and city. Okay, you get something like that. Okay, so you try practicing, you try implementing that.
Speaker A
Let me take this off. Okay, and after that, let me go for taking the clustered bar chart.
Speaker A
Clustered bar chart. So, here let me add the lane type. Lane type, average transit days.
Speaker A
Average transit days. Sorry, variance. Okay, let me add the variance. In the tool tip, we can add on-time percentage and Oh sorry.
Speaker A
So, in the tool tip, let me add on-time percentage and delivered shipments. Okay, delivered shipment. Let me click on focus mode.
Speaker A
So, here if you see the lane type is regional. Okay, next one direct lane.
Speaker A
Long haul, cross dock, and last mile. So, what do we see here? Average transit days four, on time is only 15%. Nothing.
Speaker A
15% only 15%. Change it into the percentage. Only 15%. Okay? And how many of them are delivered? 33.
Speaker A
Okay? Only 33 are delivered. Enable the data labels. Okay. Now, now what do we need to do here? Let me go for creating a table vision.
Speaker A
Okay? So, let me go for creating a table vision. Okay, what we can add in the table visual? So, let me add a route.
Speaker A
Route ID. Okay, what is the route ID? And what is the lane name? Route region.
Speaker A
Next one is distance mile. How many miles it traveled? Delivered shipments. So, how many of them are delivered?
Speaker A
And planned transit. Planned transit. What is the average transit? Average transit days. Average transit variance.
Speaker A
Okay? And on time. On time percentage. And SLA breach. And SLA breach percentage. So, what do we need to do? Again, we need to go here into the data format and make it into the percentage.
Speaker A
SLA breach also into the percentage. So, let me click on focus mode. I think we can uh uh into the visual column headers.
Speaker A
Let us change the background color of the column headers and the text to white.
Speaker A
Okay. So, let me click on focus mode. Let me increase the font size. Okay. Now, we are able to see that.
Speaker A
So, what is the route ID? Lane name. So, what is the distance it traveled?
Speaker A
How many shipments are delivered? What is the plan 4 days, but it is more than 4 days. And what is the variance? 2.6 Z6. And what is the on-time? 40% at least it is better. 40% SLA breaches 80
Speaker A
40%. So, if I click on this making ascending or descending order, so we will see few of them are on-time, okay? Los Angeles to Portland is on-time delivered. Probably it is less than that. 100%.
Speaker A
Okay? Only two of them are 100%. Again, Los Angeles to Las Vegas. It is on-time percentage.
Speaker A
Okay, this is on-time percentage. So, this is the routes. Okay, this is about the routes.
Speaker A
The last and final one in the visualization. Okay, let me go for the first one. And let me click on the duplicate.
Speaker A
Okay? The last and final one is revenue. Revenue you revenue and fuel. Revenue and fuel.
Speaker A
So, this is completely on the sales. Okay, this is completely on the sales. Let me click on this. The first one let me add the KPI is freight revenue.
Speaker A
Okay, let me take this off freight revenue. Okay? And the next one is total shipments.
Speaker A
In the reference label, I can add total reference. Total shipments. Total shipments. Okay. So, let me increase the size.
Speaker A
And next one is fuel surcharges. Fuel surcharge. Okay, 19.5 19.95. And in the reference, we can add percentage.
Speaker A
Fuel surcharge percentage. Okay, 0.08, but it is So, when we turn that So, we can go for changing that into value.
Speaker A
2% I think uh decimal 2. Okay, we need to go to general data format. Okay, fuel charges, we do not have that. Okay, no problem. Okay?
Speaker A
So, I can go for currency. Here, I can change the currency. Okay, thousand separator, only decimal.
Speaker A
Okay? So, this is what we got into the currency. General. Two decimal. Okay, this is the currency.
Speaker A
So, the next one is total revenue. This is the main driving thing. Everybody likes it. Okay, what is the revenue? How much money that someone earned? Okay, let me take this off. Total revenue.
Speaker A
Okay, $247.25 million. dollars. Next one is revenue per kg. So, total revenue per kg.
Speaker A
Okay 16.95. Next one, total Next one, we can go for total revenue. I think we already added total revenue. So, here I can add uh lost shipment revenue. So, if I want to add. Okay, in the total revenue, what I
Speaker A
can do? I can take the SLA status. I can add SLA status over here.
Speaker A
Okay? So, here I can go for breach. Okay? I can go for breach. Or uh ship- Okay, it is not the SLA, it is shipment status.
Speaker A
How many of them are lost? $37.01 million dollars lost in the shipments. And here let me go for SLA. If you want to have SLA penalty, yes we can have it. So if you would like to have it.
Speaker A
Okay. That's good. So what is the next thing? Okay? So here let us have in the on-time delivery. So let us have this Let us have the same thing. Okay?
Speaker A
Let us Let us have the same thing in here. So here let me go for the total shipments.
Speaker A
Okay? So we are focusing mainly on the revenue. So I can take the total shipments by revenue.
Speaker A
Okay? Total revenue. Okay? So I can go for total revenue. So what is the status? Not the status. I think we need to add the shipment status.
Speaker A
Shipment status and I can take this off. Just give me 1 second. Give me 1 moment.
Speaker A
So here we got what is the shipment, how much revenue that we earned from the total shipment. For example, in transit we need to earn amount some amount.
Speaker A
Okay? What is the lost shipment? How many are returned shipment? So actually we did not calculate one thing. Okay? So we should have calculated what is the total revenue minus returned units.
Speaker A
Okay? Total revenue minus returned shipments. So, we ordered a product from e-commerce website. Okay? So, in in India or throughout all over the world, many people order from many websites and that e-commerce website also get returned products.
Speaker A
So, they also need to calculate that. Okay? They also need to calculate that. So, next one is Okay? Next one is total revenue by month.
Speaker A
So, here let me go for adding total revenue. Okay? Total revenue by month. Okay? And the last one is fuel surcharge by mode.
Speaker A
Okay? Transport to mode. Okay? I can directly add fuel surcharges. Fuel surcharges. Okay? So, do we have anything here? Okay, let me I think we have a filter. Do we have anything filter over here?
Speaker A
Okay. Okay, here we are only discussing about the road and railways, so that is the reason we are not able to see that.
Speaker A
Okay? Airways is zero, completely zero. Okay? Airways is completely zero. Okay? All right. So, we added this.
Speaker A
Okay? Free charges. Okay? So, let me squeeze something. Let me add any other visual over here.
Speaker A
Can I arrange? Okay. So, let us also add revenue per kg by freight. So, let me copy this.
Speaker A
Let me paste over here. So, what is that? Total revenue. Okay? So, let me go for taking this off.
Speaker A
Uh let me take this off. So, what we can do here? So, I can add freight type.
Speaker A
Freight type. Next one, revenue per kg. Revenue per kg into the x-axis. And in the tool tip, I can add freight revenue.
Speaker A
Okay? Let me click on focus mode. So, what we can see here So, what is the parcel? Parcel is the revenue per kg is 17.58 dollars.
Speaker A
And freight revenue is this is the amount. Let me change the color. Okay. So, this is revenue per kg. Okay, this is by revenue per kg and freight revenue. How much are we earning?
Speaker A
Okay, let me increase the size. Okay. Next one revenue at risk every year. Okay, revenue at risk.
Speaker A
So, let me take the stacked column chart. So, here what we can add is So, let me add year.
Speaker A
Year into the X-axis. Lost revenue into the So, let me add lost revenue. Do we have something lost revenue?
Speaker A
Uh lost revenue. I think we do not have a lost revenue. Actually, we need to create that. So, let me try creating it in the Let me try creating in the So, let me try creating in the save.
Speaker A
Okay, let me try creating in the semantic model. So, because we missed one or two calculations, let me go to semantic model. Where is that semantic model?
Speaker A
Click on this. Yeah, this one is the semantic model. So, we can't make any changes. We can't add any measure. Okay, let me try adding any measure. New measure. Okay, let me try this.
Speaker A
So, if I can add the measure or not. So, what do we to add? Lost shipments and canceled revenue. Probably the canceled revenue can also be based on the shipment status.
Speaker A
Okay? So, we have two things in the shipment status, lost and canceled. So, we can add how much is lost, how much is canceled. Let me go for pasting this.
Speaker A
Okay, now let me click on okay. Yes, I created lost revenue. So, only measures can be created, but calculated columns cannot be created when we go for uh the mode direct query or direct lake.
Speaker A
It takes a lot of time because it is directly hitting the direct lake. Okay? Yes, the lost revenue is added.
Speaker A
So, here in this visual, let me add lost revenue. Okay? And the next one is let me go for creating another one, new measure.
Speaker A
That is canceled. Okay, let me click on this. Okay? Here, let me add Oh, sorry.
Speaker A
Here, let me add canceled. Canceled order. Yes, now we got canceled order. So, what we can do? In the tooltip, we can add the revenue at risk. Do we have something called as a revenue at risk?
Speaker A
Revenue at risk. Okay. So, revenue at risk, we can we can create another one.
Speaker A
Another Let me click on right click. So, revenue at risk is the combination is the uh sum of lost revenue and canceled revenue. Let me click on this.
Speaker A
In the tooltip, I can add revenue at risk. Okay, revenue at risk. So, let me click on focus mode.
Speaker A
And let me change the color coding. As columns, lost revenue and canceled order. Okay, now we got it.
Speaker A
So, go to visual again. Okay. Data labels. Increase the size. That's it. Okay. Okay.
Speaker A
And also on the Y axis. Okay, that that that is X axis. I'm just increasing the size. Okay.
Speaker A
Now, what do we see here? So, we are able to see the revenue at risk is some value we are getting.
Speaker A
So, again we need to go Okay, let we can do one thing. So, I can I can click on expand this.
Speaker A
Uh comma separated. Okay, comma separated. So, I can go for making it into the dollars currency.
Speaker A
The only thing when we go for changing anything measures or the formatting of the columns or measures, it takes a little time to load the data.
Speaker A
Okay. So, what do we see? I think it is fixed. Revenue at It is fixed, but the canceled revenue is not fixed. So, let me click on comma separated. So, it is giving the complete decimals more than two digit
Speaker A
two digits. So, let it load. So, let me change the color coding from green.
Speaker A
Okay, let me change the color coding to some other gray. Okay. Uh brighter gray. Okay. Now, what do we see? 15.8 million dollars.
Speaker A
Lost revenue I think lost revenue also we need to make changes. Okay, in 2023 revenue at risk is $2.24 million.
Speaker A
Okay, probably what is that 1 stands 100,000 10,000 lakhs 10 lakh crore. Okay. Uh 22 million. Okay, $22.4 million and lost revenue is $15.8 million total we need to add the total also. I think we would be having the total.
Speaker A
Yeah, total level if I click on this 224.4 million dollars in the year 2024 $19.26 million dollars and in 2025 and 2026 it is decreased.
Speaker A
Okay, it is decreased. So, this is what the analysis that we can do and we can give the information to the client. So, if I also enable the legend I think we would be having uh legend.
Speaker A
I think we can collapse all categories. Yes. Uh we would be having Yeah, legends are turned on.
Speaker A
Uh I think we got on the bottom. Let me go for uh top right, top left.
Speaker A
Yes, now we will see. So, what is lost? Okay, we need to increase the size text text.
Speaker A
Increase the size. Now you see, now we are able to see this. Okay, what is the lost revenue and what is the canceled revenue? What is the beauty in Power BI is for every category we have the options to make the changes.
Speaker A
For every each and every category we have options to make the changes. Yes, now we got now we understood now we prepared the report.
Speaker A
What is the next thing that we need to do? Okay. Can't we give the insights? Okay, now what do we need to do? We need to give the insights.
Speaker A
We need to give the insights. What are the insights that we can give? So, this is important.
Speaker A
Okay, Either we can directly give away the report and say the reporting is completed.
Speaker A
Or we can explain to the client stating that, "Okay, these are all the drawbacks that we have in here." So, let me go for snipping tool.
Speaker A
Let us get that in here. Okay, the pasting is not available. What I do, let me save this.
Speaker A
Okay. So here Okay. So, if I want to insert anything, I can go to view.
Speaker A
I can insert. I can insert image. Style, upload image. Browse. So, what are all the insights that we have?
Speaker A
Okay, what are all the insights that we have? Let me increase the size. Okay. So, what is that? Inside, the first insight is no carrier come close to SLA target.
Speaker A
Yes, we already observed, but we are giving it to the stakeholders. What is that?
Speaker A
No carrier come close to the SLA target. It is only 23.8%. But, the target should be in between 85 to 95%.
Speaker A
Okay, 85 to 95%. The companies, the organization maintain 85 plus percentage. That is the target percentage.
Speaker A
Even the best Horizon carriers reaches 35% only. Not even 50%. The lowest is 12.6% Cardinal Logistics.
Speaker A
Okay, this is a bad carrier. The next one is The second point is tier one carrier breaks SLA most.
Speaker A
Okay, so it is 65 64.5% versus 40% for carrier for tier three. Which is almost same as 23.3 versus 22.5.
Speaker A
Tier one allows only one day late. And carrier 4.0 million of 5.6 million penalty total.
Speaker A
So this is the total penalty based on the SLAs. If you do not reach the destination within the time, that is the penalty that you are going to get.
Speaker A
Okay. Next one. Lost revenue. Lost shipment revenue at risk. So what did we do the last one? We have taken the canceled and lost shipments. 758 shipments on an average 14.1% are lost carrying 32.2 million dollars of freight revenue.
Speaker A
Which is 5.8 times multiplied by SLA penalty. So it is more than the penalty. Another 369 were canceled orders, canceled shipments.
Speaker A
Which is contributing 15.3 million dollars. Okay. Next. High value shipments are not delivered. Okay, tier one brand.
Speaker A
Band shipments over 100k are 11.4% of shipments, but 38.4% of the revenue, yet their on time is 21.7% only.
Speaker A
Even the tier one is also not shipping the bill. Okay, you see we see the speed post or something else. The speed carrier the speed transportation. We paid some amount for that speed transportation 200 or 300 dollars, but yet not that is not
Speaker A
been delivered within the promised time. So that is high value shipments are also not delivered.
Speaker A
Route plans are unrealistic. Especially the one day routes. Okay, so what is that? It is planned within one day 24 hours.
Speaker A
So we see some OTT platforms or some e-commerce website. They are promising that it should be delivered within one day. If you do not delivered within one day, that is a problem. That is waste of promising.
Speaker A
Okay, so what is that? Los Angeles to Las Vegas run 3.9 days over plan with 9.5% on time.
Speaker A
So you're not the route plans are also not correct for reaching the destination. Most late deliveries are only few days late.
Speaker A
83.5% of the late deliveries arrive 1 to 5 days late. Promising two more days would raise on time. At least it should be reached within one or two days, but it is one to five days late.
Speaker A
Probably we can improve if you remember if you that we have made what if parameters.
Speaker A
So if you go for promising two more days extra at least it would reach 50%.
Speaker A
South region and hazmat freight performance are worst. Okay, South region. Okay, that is worst.
Speaker A
And same some customer sector get worst service. Automotive get 20%. Retail get 28. Not even 50%.
Speaker A
There is no improvement over time. Okay, what is it? In the year 2023 it is 22.9.
Speaker A
24 it is 24% 25 25% 26 23.4% is no improvement. So when you see the data for last couple of years, four or five years, what do you need to do? The next time at least you need to improve
Speaker A
another 20% more. Or 25% is at least you need to reach 50% on time.
Speaker A
Fuel adds $19.9 million. Many shipments are priced below the lane averages. Road brings 63.5% of the fuel and rail the rest.
Speaker A
Okay, none. Air pays none. 40s 2.6% of the shipments have freight rate below the lanes average rate.
Speaker A
So these are all the insights that we found from the asset data analyst. So these are all the insights. It's not like you just delivered or you build the report and you shared, but these are all the insights that we need to find out.
Speaker A
Okay? So this is about the complete project about Apex Freight Performance Dashboard. So what do we need to do after that?
Speaker A
Click on publish. Save this. And we can publish it to this workspace. Okay, let it load.
Speaker A
Yeah, I unmuted you. Any doubts till now? Are we good? I mean, once you practice you will understand.
Speaker A
So what do you say? Rohan Akash Anirudh Vikram. All good, sir. I mean, I just keep on speaking. Yeah, Akash, any doubts?
Speaker A
Uh, sir, can you share share that [clears throat] files? Yeah, yeah, sure. I'll share.
Speaker A
So we can practice on it. Yes, yes, I'll share the files. You can practice. So we completed full-fledged end-to-end analytics.
Speaker A
So, we covered data engineering part, data analysis part, and also BI part. So, we can build and we can publish the report so that we can give permission to the audience or to the users so that the client can
Speaker A
easily access the report. And the next step is to go for scheduling the refresh.
Speaker A
Okay, let it let it publish. So, let me open that. Okay, these are all the files. You see how many files I've made. Okay?
Speaker A
Uh what is that? Sprint board. Oh, sprint board, yeah. Now, let us see what we have completed till here.
Speaker A
Direct lake, yes, it is completed. Relationship, yes, it is completed. DAX, we have completed. Row-level security, we did not implement, but we have hidden PII. Okay, it is completed. Carrier scorecard page, yes, it is implemented.
Speaker A
Lane and revenue page, revenue by lane and region fuel surcha- surcharges, yes, it is yes, it is completed.
Speaker A
Publish the report to through fabric workspace, yes, we just now we implemented that. And what is that? Showcase GitHub repo, LinkedIn post, resume line. So, this is for you.
Speaker A
Okay? This is for you. What do you need to do? So, you need to post this. Okay? Let me also add one more task, new item. What is that item?
Speaker A
Is uh deployment pipeline, actually. Deployment pipeline. Okay? What is that? Dev, test, and production environment.
Speaker A
Okay? So, I can assign it to myself. Save this. Now, you see dev to tip.
Speaker A
Deployment pipeline. And after that, what is the what we can add? One more item.
Speaker A
Project delivery. Okay, what is that? Project to deliver. So, I can click on save this. Now, you see project delivery is also added.
Speaker A
So, I can put it on active. Project delivery and it this is also active.
Speaker A
Uh Okay, we have already report Oh, sorry. Uh let me click on replace this. Okay, it is saying that there already exists a a report with the same name. So, would you like to replace it?
Speaker A
Yes, we clicked on yes. So, what do we need to do? The next step is to create the deployment pipelines.
Speaker A
Once we create the deployment pipelines, why the deployment pipelines or CI/CD pipelines are important? To manage the reports, control the version, version controlling, and there should not be mismatch in between the whatever the changes that we do in further. So, all three dev,
Speaker A
test, and production environment are in the same line, same mapping. What instead of deploying the project directly to the production, so or publishing the project directly to the production, what do we do in the cloud, in the fabric, or in
Speaker A
the Power BI? We implement the deployment pipelines. So, based on the deployment pipelines, we deploy the project or items from from one one environment to another environment. Give me 10 more minutes, okay? Please.
Speaker A
So, let it get published. So, we can go for creating the deployment pipelines. Just give me 10 minutes.
Speaker A
So, that end-to-end project will be completed. So simple when you when you go for Azure or when you go for any other environment, so lot of lot of things involved.
Speaker A
Okay, you need to create a storage account, you need to create that, you need to create this. But when you come into the fabric, okay? The overall things, okay? The external things are so easy.
Speaker A
Internal things, we need to write the query, we need to use the SQL server, or we need to use the notebook. That would be a little we need to work on. But when we go for deploying the pipelines,
Speaker A
or when we go for securing the Yes, now it is deployed. Let me go to Now it is published, not deployed published.
Speaker A
Okay? Now you see it is published. See the time, 11:10. Okay. So let me click on deployment pipeline.
Speaker A
Here if you see, we have something called as a deployment pipelines. Okay, click on the three dots.
Speaker A
So what What is the usage of deployment pipelines? Pipeline is used to manage the workspace content through the deployment stages, continuously delivering the latest content to the users.
Speaker A
Okay? So what we can do? We can assign the new pipeline. Okay, that pipeline is different. Okay? This pipeline is different. Assign the workspace.
Speaker A
And develop and test your content, and share with the users. Okay? So let me click on the new pipeline.
Speaker A
So what is the pipeline name? So let me go for writing Apex Logistics pipeline.
Speaker A
And let me click on next. So what What are the stages that we want to have? So it depends on the organization to organization. The standard stages or the environment is development, testing, and production.
Speaker A
Whatever the project that we develop, that goes into the development. Then it goes to After there is a There are two things in the again in the testing.
Speaker A
Unit testing and user acceptance testing. So let me click on create first of all. What is unit testing? The reports are the items are the project is tested within the team. Project review by the manager or with the team leader.
Speaker A
Okay? That is unit testing. So let me click on the workspace. Okay, here what do we do?
Speaker A
In the development, we need to choose the workspace. What is that? Apex Logistics supply chain and logistics. This is the workspace that we need to go for it. Let me click on assign a workspace.
Speaker A
Okay, now this workspace is being assigned. Okay, so let me click on show more. What are all we have it in here? So we have all the data flows, lakehouse, batch loading, pipelines and all.
Speaker A
Okay, we have all these things. So I do not want to deploy everything to the test environment. Okay, in the test environment the what is that uh there would be user acceptance testing.
Speaker A
Okay, let me click on the let me click on this. I want I do not want this to be going into there. I only want the report to be in the testing. That's it. I want the report to be in the testing. That's it.
Speaker A
Only report, nothing. Okay, only report and whatever the semantic model behind it. That's it.
Speaker A
Okay, let me click on deploy. Okay, what we are deploying? We are not deploying the notebooks. We are not deploying the pipelines. Okay, we are not deploying the lakehouse. Only semantic model and the report.
Speaker A
Okay, only semantic model and report. Let me click on add note. What do I do?
Speaker A
Development development is completed. Okay. Unit testing is completed. Completed. What do we do? We go for writing the ticket number or incident number.
Speaker A
Incident number. Okay, what do we do? INC some number. Okay so now let me click on deploy this.
Speaker A
So this is being getting deployed into the testing. Actually, what do we need to do? We need to create another workspace with prefixing or suffixing with the test environment.
Speaker A
Okay, but we have not created. And here in the test environment we need to map that assign that workspace here. But we have not created, but we have deployed it. What do we need to do here? We need
Speaker A
to it all it creates it if if that workspace is not existing or assign it automatically creates the test environment.
Speaker A
Within the flow square brackets, it gives the test and Apex Logistics Group Supply Chain and Logistics. Now you see, it created Okay, what is that?
Speaker A
Now you see in the bracket it created the test. Okay, it created the test. Okay, wonderful.
Speaker A
So what do we have in here? So we have the semantic model. Okay, we have the semantic model and the reports.
Speaker A
Now you see it is giving Okay, if I click on this and if I click on compare it is comparing the item to item in this. So what it is doing? It is comparing. It is saying that uh
Speaker A
let me click on let me click okay compare. I think we would get the list.
Speaker A
Okay, data flow is existing in the dev, but it is not existing in the test.
Speaker A
Okay, so this is what we have it. Next one what do we need to do? We need to deploy it into the production. Let me click on deploy.
Speaker A
Okay, add a note. User acceptance testing. Okay, what do we do in this environment? We would add we would give permission to the users or the client.
Speaker A
So when we give permission to the client, what do we do? We do not give permission to the existing notebooks or pipelines or the lakehouse. User acceptance testing is completed.
Speaker A
Received approval. Approval from the client for production deployment. Production deployment. So what is that?
Speaker A
Incident number. Okay, INC 101. Okay, this is what we got it. Okay, I can click on deploy this.
Speaker A
Okay. Oh, sure. Your UAT testing is completed. Can you explain me one more time?
Speaker A
So user acceptance testing is done by the client. So what do we do in here?
Speaker A
So we will go to okay, let it load. I'll explain that. So now you see from the from here yes, it is successfully completed. Okay, so what happens here? So we are from the development it is passing it into the
Speaker A
test. Okay. Okay. So from the development it is passing it into the test. So what we are doing by TL or team manager so based on the conditions. What are all the conditions that we test here?
Speaker A
In the in the raw data bronze we have around 53 58 some number okay, exact I don't remember exact number but you think that there are 5358 records in the reporting do we have the same number or not?
Speaker A
Yes, we have the same number of records because none of the records have been dropped.
Speaker A
And we go for the valuation the numbers. The total revenue in the bronze layer is some value some 247 million dollars in the reporting are we getting the same 247 million dollars or not?
Speaker A
Okay, we go for testing it in within by the within the team. Okay, probably we call it as a project review.
Speaker A
Okay, we call it as a project review. Okay, after that we pushed into the test. So in the test what do we do? We give permission to the clients.
Speaker A
Client is going to test. Logistics team logistics operational team or the analyst from the logistics client they are going to check.
Speaker A
What again they are going to check? Whatever the Excel sheets they are looking into. Okay, they see the fuel charges.
Speaker A
Whether are we maintaining the same fuel charges or any changes in the fuel charges? In the month 202501 January the fuel charges are 0.05 in the reporting is it the same or not? They go for looking into the calculations. For
Speaker A
calculating the total revenue is the formula same or not? Okay? That is they are going to check.
Speaker A
And they are going to get back to us stating that, "Can you please change some color coding? Or can you please add some filter to it? Can you please uh add something to the page? Can you please add the title? We are confused.
Speaker A
We do not see the title. We only see the title of the page, but not in the report. Can you please add that?" Yes, we got the approval from the client stating that, "Yes, can you please move it into the
Speaker A
production." What do we do again? We go for moving it into the deploying it into the production.
Speaker A
In future Yeah, I'm sorry to interrupt you. So, if the client ask for any change, then again we have to go back to the the database which we are working and do the changes and again we have to do the
Speaker A
unit testing. Yes. So, for example, the client said that So, after looking into the data, the fuel charges of uh 10th to month, it should not be uh 0.05, it should be 0.6. For example, 0.63.
Speaker A
What he said? The client said, "I have updated the Excel file. Can you please refresh the data?" Again, what do we do? We go back to the We go back to the bronze layer.
Speaker A
Okay? We go back to the calculation. We refresh the pipeline. So, for example, if he changed, automatically that change is going to pick it. Because we we did not maintain any manual changes. Everything is pipeline.
Speaker A
If the data in the source changes, automatically here the pipeline is going to pick the changes.
Speaker A
Okay? It is going to pick the changes. Again, we need to go back to the If there is any changes in the calculations, yes, we need to go back to the the report and we need to change the
Speaker A
calculations. Okay? Again, what do we do? We do not directly publish into the test environment. We publish into the dev environment.
Speaker A
Then by the deployment pipelines, it goes into the test environment. Okay? If you directly publish, into there will not be any equal in between the query or the code will not be equal or the procedure or the flow will not be
Speaker A
there is a dist- there is a difference in between the dep- development and test environment.
Speaker A
So, here so in order to maintain that version control we go for deployment pipelines. Again, from here what do we do?
Speaker A
Yes, we got we go for deploying into the production environment. What we are trying? We are not deploying everything into the test or into the production. What whichever the item that the user or the client need to see, we are only
Speaker A
deploying that. For example, the client is keen. He wants to see he's a data engineer, for example. Logistics team is a data engineer. He wants to see that.
Speaker A
What he wants to see? Can you please uh go I would like to see the incremental load, incremental pipeline. What do we do? We click on deploying that.
Speaker A
Okay, click on deploy. So, that incremental load will also get deployed. Okay, that pipeline or notebooks will also get deployed.
Speaker A
So, this is how the deployment and testing is going to happen in the real time.
Speaker A
With the with n number of records. Yes, with n number of records. There would be like they are going to check. If you see here, what do we have here?
Speaker A
So, we have overview. So, So, what do we do here? We have something called as a uh carrier carrier data.
Speaker A
Okay? So, they are going to check it by the carrier. Okay, individual carriers. Okay? Or they are going to check it by the routes.
Speaker A
Okay? The north region is Okay, this is what the revenue for the north region. They go by the region.
Speaker A
Okay? Based on the region whether the values are correct or not. Based on the freight type, the values are correct or not. Based on the carrier type, the values are correct or not. So, they follow different set of rules.
Speaker A
So, if for example, there there is a percentages in the there is a percentage of testing the data.
Speaker A
Out of 100% I'm going to give for the transportation mode 25% by year 25%.
Speaker A
Okay, let me write it down. By year 25%. By transport mode 25%. By carrier 25%.
Speaker A
By freight type 25%. I checked by the year the values are good. So, that testing passed 25%. So, what I can update as a client what I update, 25% of the testing is completed. The values are matching by the year.
Speaker A
Okay, the values are matching by the year. Next transport mode. Another 25%. I also checked by the transportation mode. Yes, the values are matching. So, I map it as 50% completed.
Speaker A
Okay, again carrier type. 75% is completed. The next 25% I'm going to validate more in detail.
Speaker A
Okay, apple to apple. Ball to ball. I'm going to I'm going to test it to lane name from the source, lane name in the reporting. If they are matching or not.
Topics:Power BIDAXlogistics analyticssemantic modelMicrosoft Fabricmedallion architectureDirect Lakedeployment pipelinesSLA analysisBI developer interview











