In the business world, decisions are made using insight obtained from large data sets. Real-world data is often chaotic, so the process follows a structured workflow that involves: cleaning the messy data, properly modelling it, then applying intelligent calculations with DAX in order to create interactive dashboards that highlight what matters the most.
Handling Messy data.
Power BI's Power Query editor is the main tool we use to clean data, since raw data is rarely clean. It often comes with missing values, inconsistent data formats, duplicate values, and mismatched columns as well.
The following actions are taken to clean our data:
- Removing duplicates by clicking transform data, highlighting the suspect column(s), and clicking remove duplicates on the home option.
- Managing missing values by filling the blanks with averages or the median for the data.
- Standardizing text. using proper case and uppercase/lowercase where applicable. Work becomes easier and more accurate when we do as much transformation as possible at the transformation stage.
Building a data model.
We now create a logical structure of tables that defines how different tables are related and connected in order to support efficient analysis and reporting. This is called a schema, with the most commonly used one being called a star schema.
On the left pane, we click model view, then create relevant relationships with the fields. The fact table is always at the centre, while other dimension tables are connected via relationships.
There are one-to-one relationships, one-to-many relationships and hierarchies.
DAX
Data analysis expressions create meaningful and insightful metrics from cleaned data. For example, to get total sales data, we use the following:
Total Sales = SUM(Sales[Amount])
DAX hardcodes business metrics like costs, performance of products and others so that dashboards not only show numbers but answer big business questions like products with the best margins, growth trends, and seasonal adjustments, among other key metrics.
Dashboards.
Dashboards are the final product of the entire process, and they are decision tools.
They tell a story and give information on the most important KPI's. Thus, we use these kpi's and cards with conditional formating . We must also include interactive elements like slicers. We include graphs that focus on trends , give significant comparisons as well as edge cases and outliers. Dashborads therefore explain data in a visual form to non technical users.
The dashboard must always be easy to understand, fit in one page therefore avoiding clutter.
Top comments (0)