PBI Reporting Wiki - User contributions [en-gb]

TSA Dashboard

2026-04-08T12:23:34Z

Hendry.mwamburi: /* Data Transformation Applied */

== Summary ==
The TSA Report is a Power BI dashboard designed to provide comprehensive visibility into course bookings, participation, and revenue performance for training services delivered to corporate clients (companies).

It supports exploratory analysis across the following core dimensions:

Courses – Individual training programs and their performance
Booked Courses – Volume of confirmed bookings
Course Groups – High-level categories of training
Companies – Customer-level breakdown, including which courses each company has purchased
Total Revenue – Overall and segmented income generated

The dashboard aggregates data from the orders and order courses tables, focusing exclusively on confirmed/processed orders (where order status >= 1).

== Tables & Columns Included ==
{| class="wikitable"
|+
!Database
!Table
!Columns
|-
|tsa_reporting_db
|order
|id,order_status,company_id,user_id,ordered_by,currency,created_at,updated_at,deleted_at,confirmed_at,Date,Year

|-
|tsa_reporting_db
|companies
|id,name 

|-
|tsa_reporting_db
|course
|id,name 
|-
|tsa_reporting_db
|order bookings
|id,order_course_id,user_id,training_status, created_at, updated_at, deleted_at

|-
|tsa_reporting_db
|order courses
|id,order_id,course_id,course_location_id,price_per_person,min_price,created_at,updated_at,deleted_at

|-
|tsa_reporting_db
|course prices
|id,course_id,currency,price,min_price,created_at,updated_at

|-
|tsa_reporting_db
|course page groups
|id,course_page_id,course_group_id,Course groups

|-
|tsa_reporting_db
|users
|id,first_name,last_name,username, company_id,site_id,updated_at,deleted_at

|-
|tsa_reporting_db
|invoince exchange rates
|id,invoice_id,from_currency,to_currency,exchange_rate,created_at,updated_at

|-
|tsa_reporting_db
|invoince exchange rates
|id,invoice_id, order_id, from_currency,to_currency, exchange_rate,created_at
|}

== Filters ==
{| class="wikitable"
|+
! style="width: 25%;" |Filter
! style="width: 75%;" |Description
|-
|Company
|Helps the user filter values by company

|-
|Course Group
|Helps the user filter values by course Group

|-
|Date
|Helps the user filter values by date range
|}

== Data Transformation Applied ==
[To be added]
{| class="wikitable"
|+
!Query
!Calculations
!Description
|-
|
|
|
|}

== Calculations ==
{| class="wikitable"
! style="width: 20%;" |Reference
! style="width: 40%;" |Calculation
! style="width: 40%;" |Description
|-
|Booked Courses
|Booked Courses =
CALCULATE(

DISTINCTCOUNT(order_courses[course_id]),

 FILTER(

 orders,

orders[order_status] >= 1

 )

)
|The measure counts distinct course_id values from the order_courses table but only for orders whose status indicates they are '''confirmed or processed'''.

|-
|Total Income
|Total Income =
VAR _total_person =

 CALCULATE(

 SUMX(

order_courses,

order_courses[price_per_person] * [Users With Booked Courses]

 ),

orders[order_status] >= 1,

NOT(ISBLANK(order_courses[id]))

 )

VAR _minprice =

 CALCULATE(

SUM(order_courses[min_price]),

orders[order_status] >= 1,

NOT(ISBLANK(order_courses[id]))

 )

RETURN

 _total_person + _minprice
|'''The measure is''' used to calculate the '''total revenue generated from booked courses'''.
It combines:

Income generated '''per person per course'''

Additional '''minimum course pricing'''

Only '''valid orders''' (orders with order_status >= 1) are included in the calculation.

|}This table shows all individual orders for Courses delivered to Customers (Companies).
{| class="wikitable"
|'''Column'''
|'''Description'''
|-
|'''Order ID'''
|Unique identifier for each booking/order
|-
|'''Date'''
|The date the order was placed or the course was scheduled
|-
|'''Company'''
|Name of the customer/company that booked the training.
|-
|'''Course Name'''
|Name of the training course (e.g. '''General Security Awareness Training (GSAT)''')
|-
|'''Course ID'''
|Internal code for the course.
|-
|'''Course Group'''
|Category/group the course belongs
|-
|'''Price Per Person'''
|Cost charged per participant for the training
|-
|'''Users With Booked Courses'''
|Number of people who attended/booked this specific order.
|-
|'''Min Price'''
|This is the fixed price for the course.
|-
|'''Total Income'''
|Revenue generated from that specific order (in this data per row)
|}

TSA Dashboard

2026-04-08T12:16:06Z

Hendry.mwamburi: /* Calculations */

TSA Dashboard

2026-04-08T12:15:41Z

Hendry.mwamburi: /* Calculations */

== Summary ==
The TSA Report is a Power BI dashboard designed to provide comprehensive visibility into course bookings, participation, and revenue performance for training services delivered to corporate clients (companies).

It supports exploratory analysis across the following core dimensions:

Courses – Individual training programs and their performance
Booked Courses – Volume of confirmed bookings
Course Groups – High-level categories of training
Companies – Customer-level breakdown, including which courses each company has purchased
Total Revenue – Overall and segmented income generated

The dashboard aggregates data from the orders and order courses tables, focusing exclusively on confirmed/processed orders (where order status >= 1).

== Tables & Columns Included ==
{| class="wikitable"
|+
!Database
!Table
!Columns
|-
|tsa_reporting_db
|order
|id,order_status,company_id,user_id,ordered_by,currency,created_at,updated_at,deleted_at,confirmed_at,Date,Year

|-
|tsa_reporting_db
|companies
|id,name 

|-
|tsa_reporting_db
|course
|id,name 
|-
|tsa_reporting_db
|order bookings
|id,order_course_id,user_id,training_status, created_at, updated_at, deleted_at

|-
|tsa_reporting_db
|order courses
|id,order_id,course_id,course_location_id,price_per_person,min_price,created_at,updated_at,deleted_at

|-
|tsa_reporting_db
|course prices
|id,course_id,currency,price,min_price,created_at,updated_at

|-
|tsa_reporting_db
|course page groups
|id,course_page_id,course_group_id,Course groups

|-
|tsa_reporting_db
|users
|id,first_name,last_name,username, company_id,site_id,updated_at,deleted_at

|-
|tsa_reporting_db
|invoince exchange rates
|id,invoice_id,from_currency,to_currency,exchange_rate,created_at,updated_at

|-
|tsa_reporting_db
|invoince exchange rates
|id,invoice_id, order_id, from_currency,to_currency, exchange_rate,created_at
|}

== Filters ==
{| class="wikitable"
|+
! style="width: 25%;" |Filter
! style="width: 75%;" |Description
|-
|Company
|Helps the user filter values by company

|-
|Course Group
|Helps the user filter values by course Group

|-
|Date
|Helps the user filter values by date range
|}

== Data Transformation Applied ==
[To be added]
{| class="wikitable"
|+
!Query
!Calculations
!Description
|-
|
|
|
|}

== Calculations ==
{| class="wikitable"
! style="width: 20%;" |Reference
! style="width: 40%;" |Calculation
! style="width: 40%;" |Description
|-
|Booked Courses
|Booked Courses =
CALCULATE(

DISTINCTCOUNT(order_courses[course_id]),

 FILTER(

 orders,

orders[order_status] >= 1

 )

)
|The measure counts distinct course_id values from the order_courses table but only for orders whose status indicates they are '''confirmed or processed'''.

|-
|Total Income
|Total Income =
VAR _total_person =

 CALCULATE(

 SUMX(

order_courses,

order_courses[price_per_person] * [Users With Booked Courses]

 ),

orders[order_status] >= 1,

NOT(ISBLANK(order_courses[id]))

 )

VAR _minprice =

 CALCULATE(

SUM(order_courses[min_price]),

orders[order_status] >= 1,

NOT(ISBLANK(order_courses[id]))

 )

RETURN

 _total_person + _minprice
|'''The measure is''' used to calculate the '''total revenue generated from booked courses'''.
It combines:

Income generated '''per person per course'''

Additional '''minimum course pricing'''

Only '''valid orders''' (orders with order_status >= 1) are included in the calculation.

|-
|Booked Courses
|Booked Courses =
CALCULATE(

DISTINCTCOUNT(order_courses[course_id]),

 FILTER(

 orders,

orders[order_status] >= 1

 )

)
|The measure counts distinct course_id values from the order_courses table but only for orders whose status indicates they are '''confirmed or processed'''.

|}

TSA Dashboard

2026-04-07T13:32:35Z

Hendry.mwamburi: /* Calculations */

== Summary ==
The TSA Report is a Power BI dashboard designed to provide comprehensive visibility into course bookings, participation, and revenue performance for training services delivered to corporate clients (companies).

It supports exploratory analysis across the following core dimensions:

Courses – Individual training programs and their performance
Booked Courses – Volume of confirmed bookings
Course Groups – High-level categories of training
Companies – Customer-level breakdown, including which courses each company has purchased
Total Revenue – Overall and segmented income generated

The dashboard aggregates data from the orders and order courses tables, focusing exclusively on confirmed/processed orders (where order status >= 1).

== Tables & Columns Included ==
{| class="wikitable"
|+
!Database
!Table
!Columns
|-
|tsa_reporting_db
|order
|id,order_status,company_id,user_id,ordered_by,currency,created_at,updated_at,deleted_at,confirmed_at,Date,Year

|-
|tsa_reporting_db
|companies
|id,name 

|-
|tsa_reporting_db
|course
|id,name 
|-
|tsa_reporting_db
|order bookings
|id,order_course_id,user_id,training_status, created_at, updated_at, deleted_at

|-
|tsa_reporting_db
|order courses
|id,order_id,course_id,course_location_id,price_per_person,min_price,created_at,updated_at,deleted_at

|-
|tsa_reporting_db
|course prices
|id,course_id,currency,price,min_price,created_at,updated_at

|-
|tsa_reporting_db
|course page groups
|id,course_page_id,course_group_id,Course groups

|-
|tsa_reporting_db
|users
|id,first_name,last_name,username, company_id,site_id,updated_at,deleted_at

|-
|tsa_reporting_db
|invoince exchange rates
|id,invoice_id,from_currency,to_currency,exchange_rate,created_at,updated_at

|-
|tsa_reporting_db
|invoince exchange rates
|id,invoice_id, order_id, from_currency,to_currency, exchange_rate,created_at
|}

== Filters ==
{| class="wikitable"
|+
! style="width: 25%;" |Filter
! style="width: 75%;" |Description
|-
|Company
|Helps the user filter values by company

|-
|Course Group
|Helps the user filter values by course Group

|-
|Date
|Helps the user filter values by date range
|}

== Data Transformation Applied ==
[To be added]
{| class="wikitable"
|+
!Query
!Calculations
!Description
|-
|
|
|
|}

== Calculations ==
{| class="wikitable"
! style="width: 20%;" |Reference
! style="width: 40%;" |Calculation
! style="width: 40%;" |Description
|-
|Booked Courses
|Booked Courses =
CALCULATE(

DISTINCTCOUNT(order_courses[course_id]),

 FILTER(

 orders,

orders[order_status] >= 1

 )

)
|The measure counts distinct course_id values from the order_courses table but only for orders whose status indicates they are '''confirmed or processed'''.

|-
|Total Income
|Total Income =
VAR _total_person =

 CALCULATE(

 SUMX(

order_courses,

order_courses[price_per_person] * [Users With Booked Courses]

 ),

orders[order_status] >= 1,

NOT(ISBLANK(order_courses[id]))

 )

VAR _minprice =

 CALCULATE(

SUM(order_courses[min_price]),

orders[order_status] >= 1,

NOT(ISBLANK(order_courses[id]))

 )

RETURN

 _total_person + _minprice
|'''The measure is''' used to calculate the '''total revenue generated from booked courses'''.
It combines:

Income generated '''per person per course'''

Additional '''minimum course pricing'''

Only '''valid orders''' (orders with order_status >= 1) are included in the calculation.

|-
|Booked Courses
|Booked Courses =
CALCULATE(

DISTINCTCOUNT(order_courses[course_id]),

 FILTER(

 orders,

orders[order_status] >= 1

 )

)
|The measure counts distinct course_id values from the order_courses table but only for orders whose status indicates they are '''confirmed or processed'''.

|-
|Booked Courses
|Booked Courses =
CALCULATE(

DISTINCTCOUNT(order_courses[course_id]),

 FILTER(

 orders,

orders[order_status] >= 1

 )

)
|The measure counts distinct course_id values from the order_courses table but only for orders whose status indicates they are '''confirmed or processed'''.
|}

TSA Dashboard

2026-04-07T13:30:27Z

Hendry.mwamburi: /* Calculations */

== Summary ==
The TSA Report is a Power BI dashboard designed to provide comprehensive visibility into course bookings, participation, and revenue performance for training services delivered to corporate clients (companies).

It supports exploratory analysis across the following core dimensions:

Courses – Individual training programs and their performance
Booked Courses – Volume of confirmed bookings
Course Groups – High-level categories of training
Companies – Customer-level breakdown, including which courses each company has purchased
Total Revenue – Overall and segmented income generated

The dashboard aggregates data from the orders and order courses tables, focusing exclusively on confirmed/processed orders (where order status >= 1).

== Tables & Columns Included ==
{| class="wikitable"
|+
!Database
!Table
!Columns
|-
|tsa_reporting_db
|order
|id,order_status,company_id,user_id,ordered_by,currency,created_at,updated_at,deleted_at,confirmed_at,Date,Year

|-
|tsa_reporting_db
|companies
|id,name 

|-
|tsa_reporting_db
|course
|id,name 
|-
|tsa_reporting_db
|order bookings
|id,order_course_id,user_id,training_status, created_at, updated_at, deleted_at

|-
|tsa_reporting_db
|order courses
|id,order_id,course_id,course_location_id,price_per_person,min_price,created_at,updated_at,deleted_at

|-
|tsa_reporting_db
|course prices
|id,course_id,currency,price,min_price,created_at,updated_at

|-
|tsa_reporting_db
|course page groups
|id,course_page_id,course_group_id,Course groups

|-
|tsa_reporting_db
|users
|id,first_name,last_name,username, company_id,site_id,updated_at,deleted_at

|-
|tsa_reporting_db
|invoince exchange rates
|id,invoice_id,from_currency,to_currency,exchange_rate,created_at,updated_at

|-
|tsa_reporting_db
|invoince exchange rates
|id,invoice_id, order_id, from_currency,to_currency, exchange_rate,created_at
|}

== Filters ==
{| class="wikitable"
|+
! style="width: 25%;" |Filter
! style="width: 75%;" |Description
|-
|Company
|Helps the user filter values by company

|-
|Course Group
|Helps the user filter values by course Group

|-
|Date
|Helps the user filter values by date range
|}

== Data Transformation Applied ==
[To be added]
{| class="wikitable"
|+
!Query
!Calculations
!Description
|-
|
|
|
|}

== Calculations ==
{| class="wikitable"
! style="width: 20%;" |Reference
! style="width: 40%;" |Calculation
! style="width: 40%;" |Description
|-
|Booked Courses
|Booked Courses =
CALCULATE(

DISTINCTCOUNT(order_courses[course_id]),

 FILTER(

 orders,

orders[order_status] >= 1

 )

)
|The measure counts distinct course_id values from the order_courses table but only for orders whose status indicates they are '''confirmed or processed'''.

|-
|Booked Courses
|Booked Courses =
CALCULATE(

DISTINCTCOUNT(order_courses[course_id]),

 FILTER(

 orders,

orders[order_status] >= 1

 )

)
|The measure counts distinct course_id values from the order_courses table but only for orders whose status indicates they are '''confirmed or processed'''.

|-
|Booked Courses
|Booked Courses =
CALCULATE(

DISTINCTCOUNT(order_courses[course_id]),

 FILTER(

 orders,

orders[order_status] >= 1

 )

)
|The measure counts distinct course_id values from the order_courses table but only for orders whose status indicates they are '''confirmed or processed'''.

|-
|Booked Courses
|Booked Courses =
CALCULATE(

DISTINCTCOUNT(order_courses[course_id]),

 FILTER(

 orders,

orders[order_status] >= 1

 )

)
|The measure counts distinct course_id values from the order_courses table but only for orders whose status indicates they are '''confirmed or processed'''.
|}

TSA Dashboard

2026-04-07T13:29:49Z

Hendry.mwamburi: /* Calculations */

TSA Dashboard

2026-04-07T10:02:12Z

Hendry.mwamburi: /* Filters */

TSA Dashboard

2026-04-07T09:59:10Z

Hendry.mwamburi: /* Filters */

TSA Dashboard

2026-04-07T09:58:48Z

Hendry.mwamburi: /* Filters */

TSA Dashboard

2026-04-07T09:30:52Z