Vendor Score Rank
EconEmployee · Vendors
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 __current = [Vendor Weighted Score]
VAR __candidates = CALCULATETABLE(VALUES(VendorScorecard[vendor]), REMOVEFILTERS(VendorScorecard[vendor]))
VAR __scores = ADDCOLUMNS(__candidates, "__s", VAR __v = VendorScorecard[vendor] RETURN CALCULATE([Vendor Weighted Score], REMOVEFILTERS(VendorScorecard[vendor]), VendorScorecard[vendor] = __v))
RETURN IF(ISBLANK(__current), BLANK(), RANKX(FILTER(__scores, NOT ISBLANK([__s])), ROUND([__s], 8), ROUND(__current, 8), DESC, DENSE)))Direct dependencies: Vendor Weighted Score