Secure Commission Reporting Portal

01 Executive Summary

In freight brokerage, commission is the most sensitive number in the business. Agents are paid a share of the profit on every load they book, teams carry their own expenses, and executives take their own split. Before this project, these numbers were worked out by hand — and every person could not be given the same file without exposing everyone else's pay.

I built a single Power BI report that calculates every commission automatically from load-level data and enforces security at two levels. Row-level security makes sure each person only sees their own data. Page-level security makes sure each person only sees the pages their role is allowed to open. A custom landing page ties it together: users pick a report from a list that only shows what they can access, click one arrow, and land on their own numbers.

— Project at a Glance

02 The Challenge

• Highly sensitive data. Agents must never see another agent's loads or pay, and managers must only see their own team.
• Different users need different pages. Power BI has no built-in page-level security, so one report normally shows the same pages to everyone.
• Multi-step commission logic. Load profit, profit adjustments, a 65% team split, team expenses and separate CEO and CFO shares all had to be calculated in the right order.
• Negative-profit loads. Some loads lose money, and those losses must correctly reduce the commission instead of being ignored.
• Trust and transparency. Every payout had to be explainable down to the load, so agents could trust their check.

03 Security Architecture: RLS + Page-Level Security

Security was the core of this project. It works on two layers that run together every time someone opens the report.

Layer 1: Row-Level Security (who sees which data)

• Security mapping table: each user's login email is linked to their role, their team and their agent key.
• Dynamic RLS with USERPRINCIPALNAME(): Power BI reads who is logged in and filters the data automatically.
• Filter propagation: the security filter flows from the user table through the Team and Agent dimensions to the load fact table.
• Role scopes: executives see all teams and agents, managers see only their own team, agents see only their own loads.
• Tested before release: each role was checked with "View as role" in Power BI Desktop and Service to confirm no data leaks.

Layer 2: Page-Level Security (who sees which pages)

• Page access table: lists which report pages each role may open, and is itself protected by RLS.
• Secure navigation list: the landing page's report list is built from that table, so a user only ever sees the pages they are allowed to open.
• Dynamic arrow button: a DAX measure returns the selected page name and the button uses conditional page navigation to open it.
• Hidden pages: all report pages are hidden from the page tabs, so the landing page is the only way in.
• Data still protected underneath: even if a page were reached another way, RLS still limits the data to that user's own rows.

Who Sees What

04 Commission Logic in DAX

All commission rules live in DAX measures, so they are calculated the same way for every user, every time. Each step builds on the one before it:

05 Inside the Report

User Experience Design

• Guided landing page: a "How it works" panel explains the three steps (choose, click, switch), so new users need no training.
• Consistent navigation: a header button bar on every page plus a back arrow to return home.
• Clear wording: messages like "You will only see the reports you have access to" tell users exactly why their list looks the way it does.
• Matching layout: filters at the top, KPI cards, then detail tables — the same on every page.

06 Business Impact

07 Full List of Deliverables

08 Skills Demonstrated