Bicycle.LLCSchedule a call

← Dashboard collection

Choose dashboard — Workforce Economics

people analytics dashboard

Workforce Economics

Return-on-people, rewards, benefits, and HRIS vendor measures.

6 pages · Power BI

These dashboards use synthetic workforce data. Values illustrate the report design.

Choose page — 4 Benefits

Selected page

4 Benefits

DAX definitions

Exact source expressions and direct measure dependencies for the selected page.

Annual Benefits per Employee

EconEmployee · Benefits


VAR __benefitIds = VALUES(BenefitsPlan[employeeId])
VAR __people = FILTER(EconEmployee, NOT ISFILTERED(BenefitsPlan) || EconEmployee[employeeId] IN __benefitIds)
VAR __ids = SELECTCOLUMNS(__people, "__id", EconEmployee[employeeId])
VAR __plans = FILTER(BenefitsPlan, BenefitsPlan[employeeId] IN __ids)
VAR __n = COUNTROWS(__people) + 0
VAR __all = ALL(EconEmployee)
VAR __allN = COUNTROWS(__all) + 0
VAR __a = SUMX(__people, EconEmployee[revenueAttributedFte]) + 0
VAR __allA = SUMX(__all, EconEmployee[revenueAttributedFte]) + 0
VAR __base = SUMX(__people, EconEmployee[basePay]) + 0
VAR __bonus = SUMX(__people, EconEmployee[bonus]) + 0
VAR __benefits = SUMX(__people, EconEmployee[benefitsCost]) + 0
VAR __annualPay = __base + __bonus
VAR __rewards = SUMX(__people, EconEmployee[totalRewards]) + 0
VAR __allPay = SUMX(__all, EconEmployee[basePay] + EconEmployee[bonus]) + 0
VAR __allBenefits = SUMX(__all, EconEmployee[benefitsCost]) + 0
VAR __months = COUNTROWS(FinancialPeriod) + 0
VAR __pay = (SUM(FinancialPeriod[payExpense]) + 0) * DIVIDE(__annualPay, __allPay)
VAR __ben = (SUM(FinancialPeriod[benefitsExpense]) + 0) * DIVIDE(__benefits, __allBenefits)
VAR __cost = __pay + __ben
VAR __nonpeople = (SUMX(FinancialPeriod, FinancialPeriod[operatingExpense] - FinancialPeriod[payExpense] - FinancialPeriod[benefitsExpense]) + 0) * DIVIDE(__n, __allN)
VAR __rev = (SUM(FinancialPeriod[revenue]) + 0) * DIVIDE(__a, __allA)
RETURN IF(__n < 10 || ISEMPTY(AsOf), BLANK(), DIVIDE(__benefits, __n))

Direct dependencies: None

Benefits Share of Payroll

EconEmployee · Benefits


VAR __benefitIds = VALUES(BenefitsPlan[employeeId])
VAR __people = FILTER(EconEmployee, NOT ISFILTERED(BenefitsPlan) || EconEmployee[employeeId] IN __benefitIds)
VAR __ids = SELECTCOLUMNS(__people, "__id", EconEmployee[employeeId])
VAR __plans = FILTER(BenefitsPlan, BenefitsPlan[employeeId] IN __ids)
VAR __n = COUNTROWS(__people) + 0
VAR __all = ALL(EconEmployee)
VAR __allN = COUNTROWS(__all) + 0
VAR __a = SUMX(__people, EconEmployee[revenueAttributedFte]) + 0
VAR __allA = SUMX(__all, EconEmployee[revenueAttributedFte]) + 0
VAR __base = SUMX(__people, EconEmployee[basePay]) + 0
VAR __bonus = SUMX(__people, EconEmployee[bonus]) + 0
VAR __benefits = SUMX(__people, EconEmployee[benefitsCost]) + 0
VAR __annualPay = __base + __bonus
VAR __rewards = SUMX(__people, EconEmployee[totalRewards]) + 0
VAR __allPay = SUMX(__all, EconEmployee[basePay] + EconEmployee[bonus]) + 0
VAR __allBenefits = SUMX(__all, EconEmployee[benefitsCost]) + 0
VAR __months = COUNTROWS(FinancialPeriod) + 0
VAR __pay = (SUM(FinancialPeriod[payExpense]) + 0) * DIVIDE(__annualPay, __allPay)
VAR __ben = (SUM(FinancialPeriod[benefitsExpense]) + 0) * DIVIDE(__benefits, __allBenefits)
VAR __cost = __pay + __ben
VAR __nonpeople = (SUMX(FinancialPeriod, FinancialPeriod[operatingExpense] - FinancialPeriod[payExpense] - FinancialPeriod[benefitsExpense]) + 0) * DIVIDE(__n, __allN)
VAR __rev = (SUM(FinancialPeriod[revenue]) + 0) * DIVIDE(__a, __allA)
RETURN IF(__n < 10 || ISEMPTY(AsOf), BLANK(), DIVIDE(__benefits, __annualPay))

Direct dependencies: None

Plan Enrolment Rate

EconEmployee · Plans


VAR __benefitIds = VALUES(BenefitsPlan[employeeId])
VAR __people = FILTER(EconEmployee, NOT ISFILTERED(BenefitsPlan) || EconEmployee[employeeId] IN __benefitIds)
VAR __ids = SELECTCOLUMNS(__people, "__id", EconEmployee[employeeId])
VAR __plans = FILTER(BenefitsPlan, BenefitsPlan[employeeId] IN __ids)
VAR __n = COUNTROWS(__people) + 0
VAR __all = ALL(EconEmployee)
VAR __allN = COUNTROWS(__all) + 0
VAR __a = SUMX(__people, EconEmployee[revenueAttributedFte]) + 0
VAR __allA = SUMX(__all, EconEmployee[revenueAttributedFte]) + 0
VAR __base = SUMX(__people, EconEmployee[basePay]) + 0
VAR __bonus = SUMX(__people, EconEmployee[bonus]) + 0
VAR __benefits = SUMX(__people, EconEmployee[benefitsCost]) + 0
VAR __annualPay = __base + __bonus
VAR __rewards = SUMX(__people, EconEmployee[totalRewards]) + 0
VAR __allPay = SUMX(__all, EconEmployee[basePay] + EconEmployee[bonus]) + 0
VAR __allBenefits = SUMX(__all, EconEmployee[benefitsCost]) + 0
VAR __months = COUNTROWS(FinancialPeriod) + 0
VAR __pay = (SUM(FinancialPeriod[payExpense]) + 0) * DIVIDE(__annualPay, __allPay)
VAR __ben = (SUM(FinancialPeriod[benefitsExpense]) + 0) * DIVIDE(__benefits, __allBenefits)
VAR __cost = __pay + __ben
VAR __nonpeople = (SUMX(FinancialPeriod, FinancialPeriod[operatingExpense] - FinancialPeriod[payExpense] - FinancialPeriod[benefitsExpense]) + 0) * DIVIDE(__n, __allN)
VAR __rev = (SUM(FinancialPeriod[revenue]) + 0) * DIVIDE(__a, __allA)
RETURN IF(__n < 10 || ISEMPTY(AsOf), BLANK(), VAR __eligible = COUNTROWS(SUMMARIZE(__plans, BenefitsPlan[employeeId])) + 0
VAR __enrolled = COUNTROWS(SUMMARIZE(FILTER(__plans, BenefitsPlan[enrolled] = 1), BenefitsPlan[employeeId])) + 0
VAR __used = COUNTROWS(SUMMARIZE(FILTER(__plans, BenefitsPlan[utilisation] = 1), BenefitsPlan[employeeId])) + 0
RETURN IF(__eligible < 10 || (__enrolled > 0 && __enrolled < 10), BLANK(), DIVIDE(COUNTROWS(FILTER(__plans, BenefitsPlan[enrolled] = 1)) + 0, COUNTROWS(__plans))))

Direct dependencies: None

Plan Enrolments

EconEmployee · Plans


VAR __benefitIds = VALUES(BenefitsPlan[employeeId])
VAR __people = FILTER(EconEmployee, NOT ISFILTERED(BenefitsPlan) || EconEmployee[employeeId] IN __benefitIds)
VAR __ids = SELECTCOLUMNS(__people, "__id", EconEmployee[employeeId])
VAR __plans = FILTER(BenefitsPlan, BenefitsPlan[employeeId] IN __ids)
VAR __n = COUNTROWS(__people) + 0
VAR __all = ALL(EconEmployee)
VAR __allN = COUNTROWS(__all) + 0
VAR __a = SUMX(__people, EconEmployee[revenueAttributedFte]) + 0
VAR __allA = SUMX(__all, EconEmployee[revenueAttributedFte]) + 0
VAR __base = SUMX(__people, EconEmployee[basePay]) + 0
VAR __bonus = SUMX(__people, EconEmployee[bonus]) + 0
VAR __benefits = SUMX(__people, EconEmployee[benefitsCost]) + 0
VAR __annualPay = __base + __bonus
VAR __rewards = SUMX(__people, EconEmployee[totalRewards]) + 0
VAR __allPay = SUMX(__all, EconEmployee[basePay] + EconEmployee[bonus]) + 0
VAR __allBenefits = SUMX(__all, EconEmployee[benefitsCost]) + 0
VAR __months = COUNTROWS(FinancialPeriod) + 0
VAR __pay = (SUM(FinancialPeriod[payExpense]) + 0) * DIVIDE(__annualPay, __allPay)
VAR __ben = (SUM(FinancialPeriod[benefitsExpense]) + 0) * DIVIDE(__benefits, __allBenefits)
VAR __cost = __pay + __ben
VAR __nonpeople = (SUMX(FinancialPeriod, FinancialPeriod[operatingExpense] - FinancialPeriod[payExpense] - FinancialPeriod[benefitsExpense]) + 0) * DIVIDE(__n, __allN)
VAR __rev = (SUM(FinancialPeriod[revenue]) + 0) * DIVIDE(__a, __allA)
RETURN IF(__n < 10 || ISEMPTY(AsOf), BLANK(), VAR __eligible = COUNTROWS(SUMMARIZE(__plans, BenefitsPlan[employeeId])) + 0
VAR __enrolled = COUNTROWS(SUMMARIZE(FILTER(__plans, BenefitsPlan[enrolled] = 1), BenefitsPlan[employeeId])) + 0
VAR __used = COUNTROWS(SUMMARIZE(FILTER(__plans, BenefitsPlan[utilisation] = 1), BenefitsPlan[employeeId])) + 0
RETURN IF(__eligible < 10 || (__enrolled > 0 && __enrolled < 10), BLANK(), COUNTROWS(FILTER(__plans, BenefitsPlan[enrolled] = 1)) + 0))

Direct dependencies: None

Plan Utilisation Rate

EconEmployee · Plans


VAR __benefitIds = VALUES(BenefitsPlan[employeeId])
VAR __people = FILTER(EconEmployee, NOT ISFILTERED(BenefitsPlan) || EconEmployee[employeeId] IN __benefitIds)
VAR __ids = SELECTCOLUMNS(__people, "__id", EconEmployee[employeeId])
VAR __plans = FILTER(BenefitsPlan, BenefitsPlan[employeeId] IN __ids)
VAR __n = COUNTROWS(__people) + 0
VAR __all = ALL(EconEmployee)
VAR __allN = COUNTROWS(__all) + 0
VAR __a = SUMX(__people, EconEmployee[revenueAttributedFte]) + 0
VAR __allA = SUMX(__all, EconEmployee[revenueAttributedFte]) + 0
VAR __base = SUMX(__people, EconEmployee[basePay]) + 0
VAR __bonus = SUMX(__people, EconEmployee[bonus]) + 0
VAR __benefits = SUMX(__people, EconEmployee[benefitsCost]) + 0
VAR __annualPay = __base + __bonus
VAR __rewards = SUMX(__people, EconEmployee[totalRewards]) + 0
VAR __allPay = SUMX(__all, EconEmployee[basePay] + EconEmployee[bonus]) + 0
VAR __allBenefits = SUMX(__all, EconEmployee[benefitsCost]) + 0
VAR __months = COUNTROWS(FinancialPeriod) + 0
VAR __pay = (SUM(FinancialPeriod[payExpense]) + 0) * DIVIDE(__annualPay, __allPay)
VAR __ben = (SUM(FinancialPeriod[benefitsExpense]) + 0) * DIVIDE(__benefits, __allBenefits)
VAR __cost = __pay + __ben
VAR __nonpeople = (SUMX(FinancialPeriod, FinancialPeriod[operatingExpense] - FinancialPeriod[payExpense] - FinancialPeriod[benefitsExpense]) + 0) * DIVIDE(__n, __allN)
VAR __rev = (SUM(FinancialPeriod[revenue]) + 0) * DIVIDE(__a, __allA)
RETURN IF(__n < 10 || ISEMPTY(AsOf), BLANK(), VAR __eligible = COUNTROWS(SUMMARIZE(__plans, BenefitsPlan[employeeId])) + 0
VAR __enrolled = COUNTROWS(SUMMARIZE(FILTER(__plans, BenefitsPlan[enrolled] = 1), BenefitsPlan[employeeId])) + 0
VAR __used = COUNTROWS(SUMMARIZE(FILTER(__plans, BenefitsPlan[utilisation] = 1), BenefitsPlan[employeeId])) + 0
RETURN IF(__eligible < 10 || (__enrolled > 0 && __enrolled < 10) || (__used > 0 && __used < 10), BLANK(), DIVIDE(COUNTROWS(FILTER(__plans, BenefitsPlan[utilisation] = 1)) + 0, COUNTROWS(FILTER(__plans, BenefitsPlan[enrolled] = 1)))))

Direct dependencies: None

Plan Utilisers

EconEmployee · Plans


VAR __benefitIds = VALUES(BenefitsPlan[employeeId])
VAR __people = FILTER(EconEmployee, NOT ISFILTERED(BenefitsPlan) || EconEmployee[employeeId] IN __benefitIds)
VAR __ids = SELECTCOLUMNS(__people, "__id", EconEmployee[employeeId])
VAR __plans = FILTER(BenefitsPlan, BenefitsPlan[employeeId] IN __ids)
VAR __n = COUNTROWS(__people) + 0
VAR __all = ALL(EconEmployee)
VAR __allN = COUNTROWS(__all) + 0
VAR __a = SUMX(__people, EconEmployee[revenueAttributedFte]) + 0
VAR __allA = SUMX(__all, EconEmployee[revenueAttributedFte]) + 0
VAR __base = SUMX(__people, EconEmployee[basePay]) + 0
VAR __bonus = SUMX(__people, EconEmployee[bonus]) + 0
VAR __benefits = SUMX(__people, EconEmployee[benefitsCost]) + 0
VAR __annualPay = __base + __bonus
VAR __rewards = SUMX(__people, EconEmployee[totalRewards]) + 0
VAR __allPay = SUMX(__all, EconEmployee[basePay] + EconEmployee[bonus]) + 0
VAR __allBenefits = SUMX(__all, EconEmployee[benefitsCost]) + 0
VAR __months = COUNTROWS(FinancialPeriod) + 0
VAR __pay = (SUM(FinancialPeriod[payExpense]) + 0) * DIVIDE(__annualPay, __allPay)
VAR __ben = (SUM(FinancialPeriod[benefitsExpense]) + 0) * DIVIDE(__benefits, __allBenefits)
VAR __cost = __pay + __ben
VAR __nonpeople = (SUMX(FinancialPeriod, FinancialPeriod[operatingExpense] - FinancialPeriod[payExpense] - FinancialPeriod[benefitsExpense]) + 0) * DIVIDE(__n, __allN)
VAR __rev = (SUM(FinancialPeriod[revenue]) + 0) * DIVIDE(__a, __allA)
RETURN IF(__n < 10 || ISEMPTY(AsOf), BLANK(), VAR __eligible = COUNTROWS(SUMMARIZE(__plans, BenefitsPlan[employeeId])) + 0
VAR __enrolled = COUNTROWS(SUMMARIZE(FILTER(__plans, BenefitsPlan[enrolled] = 1), BenefitsPlan[employeeId])) + 0
VAR __used = COUNTROWS(SUMMARIZE(FILTER(__plans, BenefitsPlan[utilisation] = 1), BenefitsPlan[employeeId])) + 0
RETURN IF(__eligible < 10 || (__enrolled > 0 && __enrolled < 10) || (__used > 0 && __used < 10), BLANK(), COUNTROWS(FILTER(__plans, BenefitsPlan[utilisation] = 1)) + 0))

Direct dependencies: None

Selected Plan Employer Cost

EconEmployee · Plans


VAR __benefitIds = VALUES(BenefitsPlan[employeeId])
VAR __people = FILTER(EconEmployee, NOT ISFILTERED(BenefitsPlan) || EconEmployee[employeeId] IN __benefitIds)
VAR __ids = SELECTCOLUMNS(__people, "__id", EconEmployee[employeeId])
VAR __plans = FILTER(BenefitsPlan, BenefitsPlan[employeeId] IN __ids)
VAR __n = COUNTROWS(__people) + 0
VAR __all = ALL(EconEmployee)
VAR __allN = COUNTROWS(__all) + 0
VAR __a = SUMX(__people, EconEmployee[revenueAttributedFte]) + 0
VAR __allA = SUMX(__all, EconEmployee[revenueAttributedFte]) + 0
VAR __base = SUMX(__people, EconEmployee[basePay]) + 0
VAR __bonus = SUMX(__people, EconEmployee[bonus]) + 0
VAR __benefits = SUMX(__people, EconEmployee[benefitsCost]) + 0
VAR __annualPay = __base + __bonus
VAR __rewards = SUMX(__people, EconEmployee[totalRewards]) + 0
VAR __allPay = SUMX(__all, EconEmployee[basePay] + EconEmployee[bonus]) + 0
VAR __allBenefits = SUMX(__all, EconEmployee[benefitsCost]) + 0
VAR __months = COUNTROWS(FinancialPeriod) + 0
VAR __pay = (SUM(FinancialPeriod[payExpense]) + 0) * DIVIDE(__annualPay, __allPay)
VAR __ben = (SUM(FinancialPeriod[benefitsExpense]) + 0) * DIVIDE(__benefits, __allBenefits)
VAR __cost = __pay + __ben
VAR __nonpeople = (SUMX(FinancialPeriod, FinancialPeriod[operatingExpense] - FinancialPeriod[payExpense] - FinancialPeriod[benefitsExpense]) + 0) * DIVIDE(__n, __allN)
VAR __rev = (SUM(FinancialPeriod[revenue]) + 0) * DIVIDE(__a, __allA)
RETURN IF(__n < 10 || ISEMPTY(AsOf), BLANK(), VAR __eligible = COUNTROWS(SUMMARIZE(__plans, BenefitsPlan[employeeId])) + 0
VAR __enrolled = COUNTROWS(SUMMARIZE(FILTER(__plans, BenefitsPlan[enrolled] = 1), BenefitsPlan[employeeId])) + 0
VAR __used = COUNTROWS(SUMMARIZE(FILTER(__plans, BenefitsPlan[utilisation] = 1), BenefitsPlan[employeeId])) + 0
RETURN IF(__eligible < 10 || (__enrolled > 0 && __enrolled < 10), BLANK(), SUMX(__plans, BenefitsPlan[employerCost]) + 0))

Direct dependencies: None