Forum Discussion

TAZ95's avatar
TAZ95
New Member
3 years ago
Solved

commission schema

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 - 

 

  1. Client downloads the app
  2. Client books a free demo session with one of our trainers
  3. 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”)
  4. The client / trainer books sessions out of the purchased package and our trainers train them as per the bookings
  5. 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):

 

  1. 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

 

  1. 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:

 

  1. Create a commission calculator based on above commission structure (which should be filtered by month and trainer id)

 

  1.     Using query editor - no DAX functions to be used
  2.     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=113458058849487078745&rtpof=true&sd=true

Need Help

1 Reply