POWER BI – COMPLETE BEGINNER TO

ADVANCED PROJECT BIBLE

Madhav Store E‑Commerce Dashboard | 100% Beginner Friendly | Step‑by‑Step |
Interview Ready

Introduction – Read This First

This book is written for absolute beginners. You do NOT need any prior knowledge of
Power BI, data, or analytics.

Every concept is explained in very simple language with real life examples. After
completing this document, you will not need YouTube, Google, or any other book.

This is designed like a training manual that companies give to new employees.

We will build a real project step by step: Madhav Store E‑Commerce Sales Dashboard.

Section 1: Understanding Business Intelligence (BI) from

Zero
1.1 What is Data?
Data is raw information. Example: Ram bought a shirt for 500 rupees on 10 Jan from
Delhi.

If you write 10,000 such lines, it becomes raw data. Raw data is difficult to understand.

1.2 What is Information?

When data is summarized, it becomes information. Example: Total sales in January =
2,50,000.

This is easier to understand than 10,000 rows.

1.3 What is Business Intelligence?

Business Intelligence means converting raw data into useful information so business
owners can make decisions.

Example decisions: Which product to promote? Which city is performing bad? Where is
profit high?

1.4 Why BI is Important in Companies

 Without BI, companies work on guess. With BI, companies work on facts.

BI helps reduce loss, increase profit, and grow business.

Section 2: What is Power BI? (In Very Simple Words)

2.1 Meaning of Power BI
Power BI is a software made by Microsoft.

It helps us create charts, graphs, and dashboards from data.

It is used by managers, analysts, and business owners.

2.2 Why Companies Love Power BI

It is fast.

It connects with many data sources like Excel, CSV, SQL, etc.

It looks professional.

It is easy to share.

2.3 Real Life Example

Imagine you own a shop. You want to know: which product sells most?

Instead of checking bills manually, Power BI shows a bar chart.

One look and you understand everything.

Section 3: How Power BI Works – Simple Architecture

Power BI works in 4 main steps:

1. Get Data – bring data from Excel, CSV, SQL, etc.

2. Transform Data – clean and prepare data (Power Query).

3. Model Data – connect tables and create calculations.

4. Visualize Data – build charts and dashboard.

Think of it like cooking:

Raw vegetables → Washing → Cutting → Cooking → Serving food

Section 4: Power BI Interface Explained Slowly

4.1 Report View
This is where you design your dashboard.

You drag charts, graphs, cards here.

4.2 Data View

This is where you see tables and columns.

You check if data is correct or not.

4.3 Model View

This is where you connect tables.

Without this, Power BI will get confused.

Section 5: Project Introduction – Madhav Store

Madhav Store is an e‑commerce business. They sell products online.

They want a dashboard to track:

- Sales

- Profit

- Customers

- Cities & States performance

- Categories & sub‑categories

5.1 Orders Table Explained

Order ID – unique number for each order

Order Date – date of purchase

Customer Name – who bought

State & City – location

5.2 Details Table Explained

Order ID – to match with orders table

Amount – how much money

 Profit – profit or loss

Quantity – number of items

Category – Electronics, Furniture, Clothing

Sub‑category – Phones, Chairs, Shirts

Payment Mode – UPI, Card, COD

Section 6: Importing Data – Step by Step

Open Power BI Desktop.

Click Home tab → Get Data → CSV.

Select [Link] → Click Load.

Repeat same for [Link].

After loading, you will see both tables on right side.

This means data is successfully imported.

Section 7: Power Query – Data Cleaning in Detail

7.1 What is Power Query?
Power Query is used to clean and prepare data.

Real life example: washing dirty clothes before wearing.

7.2 Why Cleaning is Important

Wrong data = wrong report.

If date is wrong, chart will be wrong.

If numbers are wrong, profit will be wrong.

7.3 Common Cleaning Tasks

Change data type (text to date, text to number).

Remove blank rows.

Remove duplicate rows.

Split columns.
 Merge columns.

7.4 Creating New Column Example

Suppose you want Total Value = Amount * Quantity.

You can create a new column using formula.

This helps in analysis.

7.5 Group By Explained Simply

Group By means summarizing data.

Example: Total sales by category.

Instead of 100 rows, you get 3 rows: Electronics, Furniture, Clothing.

Section 8: Data View – Understanding Columns

In Data View, you see tables like Excel.

You can click any column and check its type.

If Order Date is text, change it to date.

If Amount is text, change it to number.

This is important for correct charts.

Section 9: Model View – Relationships in Detail

9.1 What is Relationship?
Relationship means connection between two tables.

Example: Order ID in Orders table connects to Order ID in Details table.

9.2 Why Relationship is Needed

Without relationship, Power BI does not know which data belongs to which.

Charts will show wrong numbers.

9.3 Types of Relationships

One‑to‑Many: One order, many details (most common).

One‑to‑One: One row matches one row.

 Many‑to‑Many: Many rows match many rows (rare).

9.4 Real Life Example

One customer → many orders.

So relationship is one‑to‑many.

Section 10: What is a Dashboard?

Dashboard is one screen that shows complete business picture.

Like car dashboard shows speed, fuel, temperature.

Business dashboard shows sales, profit, customers, etc.

10.1 Why Dashboard is Important

Managers do not have time to read Excel sheets.

They want quick view.

Dashboard gives instant understanding.

Section 11: Charts Explained in Detail

11.1 Bar Chart
Used to compare values.

Example: Sales by city, profit by category.

11.2 Line Chart

Used to see trend over time.

Example: Monthly sales, yearly profit.

11.3 Pie / Donut Chart

Used to see share or percentage.

Example: Category share, payment mode share.

11.4 Cards
Used to show single big number.

Example: Total Sales, Total Profit.

Section 12: KPIs – Explained Like a Story
12.1 Total Sales
Total money earned from customers.

12.2 Total Profit

Money left after expenses.

12.3 Total Quantity

Total number of items sold.

12.4 Average Order Value (AOV)

Average money spent per order.

Example: 10 orders = 1000 rupees → AOV = 100.

Section 13: Filters & Slicers in Detail

Slicer is like remote control of dashboard.

You click Delhi → only Delhi data shows.

You click Q1 → only Q1 data shows.

All charts change automatically.

Section 14: Business Thinking (Very Important)

Power BI is not just charts.

It is about understanding business.

Always ask: What does this chart tell?

Always ask: What decision can be taken?

Example: If UP sales are low, maybe marketing is weak there.

Example: If Electronics profit is high, promote it more.

Section 15: Practice Questions

1. Create a bar chart showing sales by city.

2. Create a donut chart showing payment mode share.

 3. Show top 5 customers by profit.

4. Create a slicer for category.

5. Create monthly sales trend chart.

6. Find which state gives maximum profit.

7. Show top 3 sub‑categories.

Section 16: Interview Questions & Answers

Q: What is Power BI?

A: Power BI is a Microsoft tool to analyze data and create dashboards.

Q: What is Power Query?

A: Power Query is used to clean and transform data.

Q: What is relationship?

A: Relationship connects two tables.

Q: What is DAX?

A: DAX is formula language in Power BI.

Q: What is slicer?

A: Slicer is a filter used in report.

Section 17: DAX Formulas Explained Simply

Total Sales = SUM(Details[Amount])

This adds all amount values.

Total Profit = SUM(Details[Profit])

This adds all profit values.

Total Quantity = SUM(Details[Quantity])

This adds all quantities.

Orders Count = COUNT(Orders[Order ID])

This counts number of orders.

AOV = DIVIDE(SUM(Details[Amount]), COUNT(Orders[Order ID]))

 This calculates average order value.

Section 18: Useful Shortcuts

Ctrl + C / Ctrl + V → Copy paste visual.

Ctrl + Z → Undo.

Ctrl + Y → Redo.

Ctrl + Click → Select multiple visuals.

F5 → Refresh data.

Section 19: Resume Line

Built an interactive Power BI dashboard to analyze e‑commerce sales, profit, customers,
and state‑wise performance using real business data.
 ADVANCED DAX – COMPLETE BEGINNER
FRIENDLY BOOK
Explained in Simple Language | With Examples | Interview Ready

Introduction – Why This DAX Book is Different

This book is written for people who are scared of DAX or think it is very difficult.

Here, every formula is explained in very simple language with real life examples.

We will start from basics and slowly move to advanced concepts.

By the end, you will be confident in writing DAX yourself.

Section 1: What is DAX? (Very Simple)

1.1 Meaning of DAX
DAX stands for Data Analysis Expressions.

It is a formula language used in Power BI.

If Excel has formulas, Power BI has DAX.

1.2 Why DAX is Needed

DAX is used to create new calculations.

Example: Total Sales, Profit, Average Order Value.

Without DAX, you cannot create powerful logic.

1.3 Where DAX is Used

DAX is used in Measures.

DAX is used in Calculated Columns.

DAX is used in Calculated Tables.

Section 2: Measures vs Calculated Columns (Very
Important)
2.1 Calculated Column
Calculated column works row by row.

It is calculated when data is loaded.

Example: Profit = Amount - Cost (for each row).

2.2 Measure
Measure is calculated when you use it in a visual.

It is dynamic and changes with filters.

Example: Total Sales = SUM(Amount).

2.3 Easy Example

Calculated column = permanent value stored in table.

Measure = value calculated on the fly.

Section 3: Basic DAX Functions (Foundation)

3.1 SUM
SUM adds numbers.

Example: Total Sales = SUM(Details[Amount])

3.2 COUNT
COUNT counts numbers.

Example: Total Orders = COUNT(Orders[Order ID])

3.3 DISTINCTCOUNT
Counts unique values.

Example: Unique Customers = DISTINCTCOUNT(Orders[Customer Name])

3.4 AVERAGE
Calculates average.
 Example: Avg Profit = AVERAGE(Details[Profit])

Section 4: CALCULATE – Heart of DAX

4.1 What is CALCULATE?
CALCULATE changes the filter context.

In simple words: it tells Power BI to calculate something in a special way.

4.2 Why CALCULATE is Powerful

It allows conditions inside calculation.

Example: Sales only for Delhi.

Sales only for Electronics.

4.3 Simple Example

Sales Delhi = CALCULATE(SUM(Details[Amount]), Orders[State] = 'Delhi')

Meaning: Calculate total sales but only for Delhi.

Section 5: FILTER Function Explained

5.1 What is FILTER?
FILTER returns a table based on condition.

It is like applying filter in Excel.

5.2 Example
High Profit Sales = CALCULATE(SUM(Details[Amount]), FILTER(Details, Details[Profit]
> 1000))

Meaning: Only include rows where profit is greater than 1000.

Section 6: IF Function (Decision Making)

6.1 What is IF?
IF checks condition and gives result.

If condition is true → do this.

If false → do something else.

6.2 Example
Profit Status = IF(Details[Profit] > 0, 'Profit', 'Loss')

Meaning: If profit > 0 then Profit else Loss.

Section 7: RELATED Function (Relationship Based)

7.1 What is RELATED?
RELATED is used when tables are connected.

It brings column from another table.

7.2 Example
In Details table, show Customer Name from Orders table.

Customer = RELATED(Orders[Customer Name])

Section 8: Time Intelligence (Very Important)

8.1 Why Time Intelligence?
To compare data with time.

Example: This month vs last month.

8.2 TOTALYTD
Total Sales YTD = TOTALYTD(SUM(Details[Amount]), Orders[Order Date])

8.3 SAMEPERIODLASTYEAR
Sales Last Year = CALCULATE(SUM(Details[Amount]),
SAMEPERIODLASTYEAR(Orders[Order Date]))

Meaning: Show sales for same time last year.

Section 9: RANKX (Ranking)

9.1 What is RANKX?
RANKX is used to rank values.

Example: Top customers, top products.

9.2 Example
 Customer Rank = RANKX(ALL(Orders[Customer Name]), SUM(Details[Amount]))

Meaning: Rank customers by total sales.

Section 10: ALL and ALLEXCEPT

10.1 ALL
ALL removes filters.

Used to calculate total ignoring slicers.

10.2 ALLEXCEPT
Keeps one filter and removes others.

10.3 Example
Total Sales All = CALCULATE(SUM(Details[Amount]), ALL(Orders))

Section 11: Context – Row Context & Filter Context

11.1 Row Context
Row context works row by row.

Used in calculated columns.

11.2 Filter Context

Filter context comes from slicers and visuals.

Used in measures.

11.3 Simple Example

When you select Delhi in slicer, filter context is Delhi.

Section 12: Common Real Project Scenarios

12.1 Sales for Selected Category
Sales Category = CALCULATE(SUM(Details[Amount]), VALUES(Details[Category]))

12.2 Profit Only for Furniture

Furniture Profit = CALCULATE(SUM(Details[Profit]), Details[Category] = 'Furniture')
12.3 Top 5 Customers
Use RANKX + IF to show only top 5.

Section 13: Performance Tips (Important)

Use measures instead of calculated columns when possible.

Avoid heavy calculations in visuals.

Keep formulas simple.

Section 14: Interview Questions on DAX

Q: What is DAX?

A: DAX is a formula language used in Power BI for calculations.

Q: Difference between Measure and Calculated Column?

A: Measure is dynamic, column is static.

Q: What is CALCULATE?

A: It modifies filter context.

Q: What is FILTER?

A: It filters rows based on condition.

Q: What is RANKX?

A: It ranks values.

Section 15: Practice Exercises

1. Create measure for Total Sales.

2. Create measure for Profit for Delhi only.

3. Rank customers by sales.

4. Calculate last year sales.

5. Show only profitable orders.

Final Note
DAX becomes easy with practice.
 Do not memorize. Understand logic.

Practice daily for 20 minutes.

ADVANCED DAX – COMPLETE BEGINNER FRIENDLY BOOK

With YouTube Tutorial Explained Step-by-Step | Simple Language | Visual Thinking

Introduction – How to Use This Book

This book is written in very simple language. We will go slowly, step by step.

Every formula is explained with real-life examples and business meaning.

You DO NOT need to watch the YouTube video again. Everything is explained here
clearly.

Read one section, then open Power BI and try it. This is the best way to learn.

Section 1: What is DAX? (Very Simple)

DAX stands for Data Analysis Expressions.

It is the formula language of Power BI.

Just like Excel has formulas, Power BI has DAX.

We use DAX to calculate totals, profit, averages, rankings, and many more things.

Think of DAX as: Calculator + Brain of Power BI

Section 2: YouTube Tutorial – Explained in Simple Words

2.1 Importing Data (Orders & Customers)
In the video, the trainer first imports two Excel files: Orders and Customers.

Orders file contains order details like Order ID, Product, Quantity, Selling Price, Cost.

Customers file contains customer details like Name and Order Date.

He uses: Home → Get Data → Excel Workbook → Select file → Load.

Meaning: We are bringing raw data inside Power BI so we can work on it.

2.2 Understanding Table View

 After importing, he goes to Table View.

Table View shows data in rows and columns like Excel.

This helps us check if data is loaded correctly.

Always check your data once before writing DAX.

Section 3: First DAX – Count of Orders

3.1 Why We Need Count of Orders
Business always wants to know: How many orders did we get?

This tells us business volume.

3.2 Creating a Measure

He selects Orders table.

Then clicks New Measure.

He writes:

Count of Orders = COUNT(Orders[Order ID])

Meaning: Count how many Order IDs are present. Each Order ID is one order.

3.3 Why Order ID?

Order ID is unique (no duplicates).

So counting Order ID gives total orders.

3.4 Showing It in Report

He drags the measure into report.

Power BI shows a visual automatically.

He changes it to Table visual.

Then increases font size from formatting.

This is how we convert numbers into readable form.

Section 4: Another Way – COUNTROWS

4.1 What is COUNTROWS?
 COUNTROWS counts number of rows in a table.

Formula used:

Count of Orders 2 = COUNTROWS(Orders)

Meaning: Count all rows in Orders table. Each row = one order.

4.2 When to Use COUNTROWS

When each row is one record.

When there are no blank rows.

Section 5: DISTINCTCOUNT – When Data Has Duplicates

5.1 Why DISTINCTCOUNT?
Sometimes data has duplicate Order IDs.

Normal COUNT will give wrong answer.

So we use DISTINCTCOUNT.

5.2 Formula
Unique Orders = DISTINCTCOUNT(Orders[Order ID])

Meaning: Count only unique Order IDs. Ignore duplicates.

Section 6: Editing, Renaming & Commenting in DAX

6.1 Renaming Measure
You can click on measure name and change it.

Example: Count of Orders → Total Orders

6.2 Adding Comments

Use // to write comments in DAX.

Example:

// Calculating total orders from Orders table

Comments help other people understand your formula.

Section 7: SUM – Total Quantity

7.1 Why SUM?
Business wants to know: Total quantity sold.

7.2 Formula Used

Total Quantity = SUM(Orders[Quantity])

Meaning: Add all quantity values together.

7.3 Formatting Numbers

He noticed decimal value in quantity.

He changed decimal places to 0.

This makes quantity look like whole number.

Always format numbers properly for business users.

Section 8: Using Measures with Customer Name

He adds Customer Name to table.

Now Total Quantity shows per customer.

He sorts to see who ordered most.

This helps find top customers.

Section 9: Profit Cost Calculation

9.1 Logic
Profit Cost = Selling Price – Product Cost

9.2 Formula
Profit Cost = SUM(Orders[Selling Price]) - SUM(Orders[Product Cost])

Meaning: How much extra we earn over cost.

Section 10: Quick Measure (No Coding Way)

He uses Quick Measure option.

Selects Subtraction.
 Selects Selling Price and Product Cost.

Power BI auto-creates formula.

Quick Measure is for beginners who are scared of DAX.

Section 11: Total Profit with Quantity

11.1 Logic
Profit per item = Selling Price – Cost

Total Profit = (Selling Price – Cost) * Quantity

11.2 Formula
Total Profit = (SUM(Orders[Selling Price]) - SUM(Orders[Product Cost])) *
SUM(Orders[Quantity])

This gives total business profit.

Section 12: Measure vs Calculated Column (Very

Important)
12.1 Measure
Dynamic.

Changes with filters.

Used in visuals.

12.2 Calculated Column

Static.

Calculated row by row.

Stored in table.

Measure = On the fly, Column = Stored value

Section 13: Row Level Profit Column

13.1 Why Column?
Sometimes we need profit for each row.
13.2 Formula
Profit Column = (Orders[Selling Price] - Orders[Product Cost]) * Orders[Quantity]

Here we do NOT use SUM. Because we want row-wise calculation.

Section 14: Profit by Product

He adds Product Name and Total Profit in table.

Sorts to see which product gives highest profit.

This helps decide which product to promote.

Business Insight: Focus on high profit products.

Section 15: Working with Date – Weekday Column

15.1 Why Weekday?
To see which day has most orders.

15.2 Formula
Order Day = WEEKDAY(Customers[Order Date], 2)

2 means Monday = 1, Sunday = 7

This helps find busy days.

Section 16: Trend Analysis

He uses bar chart with Order Day and Total Orders.

Finds which day has highest orders.

This helps plan offers and staff.

Example: If Thursday has highest orders, run offers on Thursday.

Section 17: IF Function – Order Category

17.1 Logic
If Quantity < 30 → Small Order

Else → Big Order

17.2 Formula
Order Category = IF(Orders[Quantity] < 30, "Small Order", "Big Order")

IF is used for decision making.

Final Notes – Important Points

DAX is not difficult. It just needs practice.

Always understand business logic first, then write formula.

Do not memorize. Understand.

Practice daily for 20 minutes.

After this book, you are better than 80% beginners.

=== ADDITIONAL ADVANCED SECTIONS (APPENDED) ===

1. How to Design Data Model Like a Professional
Data modeling is the foundation of Power BI. Even if your DAX is perfect, a wrong data
model will always give wrong results.

In companies, data is usually divided into Fact tables and Dimension tables. Fact tables store
transactions like sales, orders, payments. Dimension tables store descriptive information
like customers, products, dates, regions.

Star Schema is the best practice. It means one central fact table connected to multiple
dimension tables. This makes reports faster and DAX simpler.

Snowflake schema means dimensions are further split into sub-dimensions. This looks
organized but makes DAX more complex and slower.

Many-to-many relationships should be avoided because they confuse Power BI and produce
unexpected results.

Always use single direction filtering from dimension to fact table. This is how companies
design models.

2. Row Context vs Filter Context – Explained Simply

Row Context means calculation happens row by row. It is mainly used in calculated
columns.
Filter Context means calculation happens based on filters from visuals, slicers, or
CALCULATE function.

Example: In a calculated column, Price * Quantity is row context. In a measure, SUM(Sales)

changes based on filters – that is filter context.

CALCULATE works by modifying filter context. This is why CALCULATE is so powerful.

Many beginners get wrong answers because they confuse row context with filter context.

3. Date Table – The Backbone of Time Intelligence

A Date Table is a separate table that contains continuous dates without gaps. It also has
columns like Year, Month, Quarter, Day.

Time intelligence functions like TOTALYTD, SAMEPERIODLASTYEAR only work properly

when you use a proper date table.

Always mark your date table as 'Date Table' in Power BI.

Without date table, your year-on-year or month-on-month analysis may be wrong.

In companies, date table is mandatory for all serious reports.

4. Power BI Service – How It Works in Real Companies

Power BI Desktop is used to build reports. Power BI Service is used to publish, share, and
manage reports.

You publish your report to a Workspace. Then you share it with users or create an App.

Managers usually view reports in Power BI Service, not in Desktop.

Gateway is used when data is on local server and needs to be refreshed in cloud.

In real companies: Data Team builds → Publishes → Business users consume.

5. How to Design Dashboard Like a Professional Analyst

A good dashboard is clean, simple, and easy to understand.

Do not use too many colors. Use 2–3 main colors.

Use bar chart for comparison, line chart for trends, pie chart for share (only few categories).

Align all visuals properly. Maintain equal spacing.

Always add meaningful titles. Avoid clutter.

Professional dashboards look simple, not flashy.

6. How Different Departments Use Power BI

Sales Team
Sales managers check top customers, top products, monthly trends to plan targets.

Marketing Team
Marketing checks campaign performance, customer behavior, and conversion rates.

Inventory Team
Inventory team checks stock movement and slow-moving products.

Finance Team
Finance checks profit, cost, and margin to control expenses.

7. Common Power BI & DAX Mistakes Beginners Must

Avoid
Using calculated column instead of measure for totals.

Wrong relationships between tables.

Not cleaning data properly.

Using SUM in calculated column when row calculation is needed.

Not formatting numbers and dates.

Not using date table.

8. Scenario Based Interview Questions (Real Company

Style)
Q1. Sales is down this month. How will you analyze?

Answer: I will compare this month with last month, check region-wise, product-wise, and customer-wise sales.

Q2. Manager wants profit by region. What will you do?

Answer: I will ensure proper relationship, create profit measure, and use region from dimension table.

Q3. Report is slow. What will you check?

Answer: Data model, unnecessary columns, relationships, and DAX complexity.