Hello, I Have been using power bi visualization tool for a while to do some basic visualization. I am trying to get better at dax and
power query and I came accros this task. I tried to solve it but I wasnt sucessful. any help would be appreciated. The details about the problem is below
We are an organisation in the fitness industry. We have employed athletes as "Fitness Trainers" who train our clients.
Operations cycle is as follows -
- Client downloads the app
- Client books a free demo session with one of our trainers
- If the client likes the service, they purchase a package (packages are considered “New” when it is the first package that the client has ever bought with the company and subsequently all packages are considered “Renewal”)
- The client / trainer books sessions out of the purchased package and our trainers train them as per the bookings
- There is an option with the clients or trainers to cancel the booking (cancellation can be charged or not charged depending on policy – charged session are as good as conducted sessions)
Details about data (attached is excel files):
- Sales - details of all packs sold to clients
Field | Remarks |
pack_id | Unique identifier of pack purchased by client |
client_id | Client unique identifier |
pack_size | Size of package purchased |
date_purchased | Date of purchase |
paid | Whether the package has been paid for (1 = paid, 0 = not paid) |
paid_date | Date of payment |
- Sessions – details of sessions conducted
Field | Remarks |
client_id | Client unique identifier |
trainer_id | Trainer unique identifier |
date_training | Date when session is conducted |
time_training | Time when session conducted (in UTC) |
pack_id | Pack from which session conducted |
status_session | Status of session – · Pending – Booked but pending to be confirmed by trainer · Confirmed – Booked and confirmed by trainer · Completed – Conducted · Cancelled(Trainer/Client)-Not Charged – Cancelled and not charged from the client’s package · Cancelled(Trainer/Client)-Charged – Cancelled and charged from client’s package |
type_session | Free = these are demo sessions not considered for trainer commission calculation Paid = Considered for trainer commission calculation |
session_rating | Star rating given by client for the session |
Commission structure is as below –
1 Base commission
- @ AED 85 for sessions from “new” packages
- @ AED 95 for sessions from “renewal” packages
Base commission for the first 15 sessions are reduced while calculating commissions – i.e. not paid to the trainer (initially new sessions are reduced and if enough new sessions are not available, reduction is done from renewal sessions)
2 Star rating bonus / penalty
- 5 star rating – bonus of AED 50 for that session
- 4 star rating – bonus of AED 35 for that session
- 2 star rating – penalty of AED 35 for that session
- 1 star rating – penalty of AED 50 for that session
3 Bonus based on total sessions
A bonus scheme is applicable as follows based on total no. of conducted sessions during a month (reduction does not affect this) –
AED 2,000 for 81 – 90 sessions
AED 2,500 for 91 – 100 sessions
AED 3,000 for 101 – 120 sessions
AED 3,500 for 121 – 150 sessions
AED 5,000 for > 150 sessions
Sessions conducted from unpaid packages are not considered for commission calculation
This is historical data from the start of business.
To do:
- Create a commission calculator based on above commission structure (which should be filtered by month and trainer id)
- Using query editor - no DAX functions to be used
- Using DAX functions – major calculations to be done using DAX
The data is too big so I modified it a little bit
https://docs.google.com/spreadsheets/d/1T1aZ5zVOhTPPqAIb8Y-PsqY1onMnDjaH/edit?usp=sharing&ouid=11345...
