Rahim Zulfiqar Ali
Advanced Sales
Analytical Dashboard
using
Data Analysis
Expressions (DAX) &
Visualizations Rules +
Tips in Microsoft Excel
Dashboard Preview
What is DAX?
Data Analysis Expressions is the native formula and query
language for Microsoft PowerPivot, Power BI Desktop and SQL
Server Analysis Services Tabular models. Wikipedia
Data Analysis Expressions (DAX) is a formula expression language
used in Analysis Services, Power BI, and Power Pivot in Excel. DAX
formulas include functions, operators, and values to perform
advanced calculations and queries on data in related tables and
columns in tabular data models. Microsoft Learn
In More Simple Words…. DAX!
DAX (Data Analysis Expressions) is a formula language used in
Microsoft PowerBI and Excel to create custom calculations and
aggregations in a data model.
It allows you to perform data analysis, data modeling, and data
manipulation tasks, such as creating calculated columns,
calculated tables, and measures.
DAX expressions are used to perform operations on the data,
and can be based on simple mathematical operations, or more
complex logic.
What is Measure?
Measures are calculations used in data analysis.
Examples commonly found in business reports
include sums, averages, minimum or maximum
values, counts, or more advanced calculations that
you create using a Data Analysis Expressions (DAX)
formula.
You can name measures whatever you want and
add them to a new or existing visualization just like
any other field.
Explicit measures = more flexibility!
While implicit measures can support some basic
scenarios, as soon as your report needs more complex
calculations, you'll have to switch to explicit measures.
However, the main advantage of using explicit
measures instead of implicit ones is their reusability.
Calculated Column & Measure in Excel | DAX
A calculated column belongs to a single table,
while a measure belongs to the whole data model.
A calculated column is evaluated in a row context
(row by row, like in an excel table), while a
measure is evaluated in the filter context.
Calculated Column
Explicit Measure
Star schema overview
Star schema is a mature modeling approach widely adopted by
relational data warehouses. It requires modelers to classify their
model tables as either dimension or fact.
Dimension tables describe business entities—the things you
model. Entities can include products, people, places, and concepts
including time itself.
The most consistent table you'll find in a star schema is a date
dimension table. A dimension table contains a key column (or
columns) that acts as a unique identifier, and descriptive columns
Fact tables store observations or events, and can be
sales orders, stock balances, exchange rates,
temperatures, etc. A fact table contains dimension key
columns that relate to dimension tables, and numeric
measure columns.
The dimension key columns determine the
dimensionality of a fact table, while the dimension key
values determine the granularity of a fact table.
STAR SCHEMA
STAR SCHEMA
Primary Key & Foreign Key (Basic concept)
Foreign Key
Fact Table
Real time transactions &
Duplicate Records
Relationship
Primary Key
Dimension Table
Unique Records
C.R.A.P.,
a design principle developed by Robin Patricia
Williams, stands for Contrast, Repetition, Alignment,
and Proximity.
By leveraging CRAP, you can consistently deliver
effective designs, whether it's for a website, a landing
page, a checkout page, an eBook, or just a banner ad.
CRAP Rule
Contrast
Contrast by
• Color
• Thickness
• Shape
• Size
• Position
• Distance
Repetition
Repeat
• Color
• Visual Elements
• Fonts
• Ideas
Alignment
Align
• Every edge of every shape with other shapes
• ALT Key
• Alignment tools
Proximity refers to visual search (i.e., your eyes scanning the
page for information). In a dashboard, you’re providing the
Proximity tools and organizing them in a way that makes sense to users
Preattentive attributes:
They help us enable our audience to see what we want
them to see before they even know they are seeing it.
Preattentive attributes are hard-wired within our visual
systems, and apply to just about everything that we can
visually perceive.
This concept of preattentive visual properties can be applied
to data visualizations in order to more effectively
communicate what we want our data to show.
Preattentive attributes
Preattentive attributes
Dashboard Design Rules
10 Design Rules / Suggestions!
1. Design with a specific goal in mind
2. Dashboard on a Single Page
3. Align the Elements (Slicers, Charts, Text Box)
4. Be Consistent with Design (Colors, Fonts, Charts)
5. Highlight the Most Relevant Information
6. Short the Numbers (Example 1.5M | 3.1B)
7. Choose the Right Colors (Theme)
8. Pick the Right Charts
9. Dashboard should present Data Storytelling for easy Decision Making
10. Show the Context (Actual vs Budget, Variances)
https://www.rahimzulfiqarali.com/
https://www.youtube.com/@ExcelBasement/
Subscribe to my YouTube Channel “Excel Basement”
