Data Collection & Table Structures

The necessary data was collected and structured in Excel Files. The exercise data was organized as a fact table and date & activity were organized as dimension tables for filtering the data.

FACT_Activity

Exercise data was imported from my Huawei watch, taking a focus on step and running. The date column is used as a key to connect the table with the activity dimension.

The steps I followed in Power Bi to transform the table for analysis puposes was:

• Removed unnecessary columns and null values
• Changed the columns to have the correct data types (Date & numbers) for later calculations

DIM_Activity

Assigns numeric keys to the different types of activities performed, in this case running and walking.

Steps followed for data cleansing and better data correlation:

Renamed both columns to have more user-friendly names

Capitalized both columns for better data quality

DIM_DATE

The date table provides a login of every activity along with the day of the week, the month and date on a string format.

Below you’ll find attached an screenshot of the data model after cleansed and prepared for PowerBI

We observe that the FACT table is connected to two dimmension tables with a connect relationship establised (1 to many) between dimmension and FACT_Activity.

You’ll also find a calculations table with no visible connections.

The following calculations were created into PowerBI using DAX (Data Analysis Expressions):

The following calculations were created in the Power BI reports using DAX (Data Analysis Expressions). To lessen the extent of coding, the re-use of measures (measure branching) was emphasized:

Average Steps – This is a simple AVERAGE function around the Steps column:
AVERAGE( FACT_Activity[Steps] )

Total Steps – This is a simple SUM function around the Steps column:
SUM( FACT_Activity[Steps] )

Steps (Running) – This is a calculation to isolate the Total Steps measure by filtering it by the “Running Activity”:

CALCULATE(
[Total Steps],
DIM_Activity[ActivityName] = “Running”
)

Steps (Walking) – This is a calculation to isolate the Total Steps measure by filtering it by the “Walking Activity”:

CALCULATE(
[Total Steps],
DIM_Activity[ActivityName] = "Walking"
)

Running % of Total – Here we are using two measures from before to find the % of steps that were done by running:

DIVIDE(
[Steps (Running)],
[Total Steps]
)

Walking % of Total – Here we are using two measures from before to find the % of steps that were done by walking:

DIVIDE(
[Steps (Walking)],
[Total Steps]
)

Total Steps (Cumulative) – Here we are re-using the Total Steps measure and using different functions to cumulatively calculate the total steps:

CALCULATE(
[Total Steps],
FILTER(
ALLSELECTED( DIM_Date ),
DIM_Date[Date]
<= MAX( FACT_Activity[Date] )
)
)

Week Over Week % Change Steps – Here we are using the Total Steps measure and using different functions, with variables, to calculate the Week over Week % Change of Steps:

VAR CurrentWeek =
CALCULATE(
[Total Steps]
FILTER(
ALL( DIM_Date ),
DIM_Date[Week of Year]
= SELECTEDVALUE( DIM_Date[Week of Year] )
)
)
VAR PreviousWeek =
CALCULATE(
[Total Steps],
FILTER(
ALL( DIM_Date ),
DIM_Date[Week of Year]
= SELECTEDVALUE( DIM_Date[Week of Year] ) – 1
)
)
RETURN
DIVIDE(
( CurrentWeek – PreviousWeek ),
PreviousWeek
)

The finish report consists of an interactive dashboard displaying graphics of the analyzed data. To enable these visualizations the calculation language DAX (Data Analysis Expressions) were used.

Let’s select now my running data and see how it compares to the general dashboard

Its curious to observe that most of my fitness activity came from running, representing 70% of the total activity, averaging 13k steps per run.

I’m definitely not a fitness guru but this is a great example of how data surrounds us in everything we do…

-Alfredo S.