Hi @Liz -
You can use LAC-W functions for this.
Here’s an example for some common use cases.
Step 1 - Create a calculated field for the YTD amount per vendor. This uses a LAC-W sumOver for partition year, and vendor.
c_vendor_ytd_amt
sumOver(amt,[truncDate('YYYY',dt),vendorname], PRE_FILTER)
Step 2 - Create a calculated field that assigns a row number across your percentilerank dimension
c_vendor_rn
// use rank function as row_number over vendor name
rank([dt DESC],[vendorname], PRE_FILTER)
Step 3 - Create a calculated field using the percentileRank LAC-W function. This does not have “Over” in the name but it is a LAC-W. The partition is the row number.
c_vendor_amt_percentilerank
percentileRank([{c_vendor_ytd_amt} ASC], [{c_vendor_rn}], PRE_FILTER)
Step 4 - This is an alt. calculation if you a symmetric percentile range (0-1 or what you see in Excel)
c_vendor_amt_percentilerank_symmetric
100*(rank([{c_vendor_ytd_amt} ASC],[{c_vendor_rn}],PRE_FILTER)-1)/(distinctCountOver(vendorname,[],PRE_FILTER)-1)
Step 5 - Filter your visual to only include 1 row per dimension.
If you are using the symmetric calc you can put this in c_vendor_ytd_amt with no filter needed.
ifelse({c_vendor_rn}=1,sumOver(amt,[truncDate('YYYY',dt),vendorname], PRE_FILTER),null)
Step 6 - Create a calculated field to show the vendor rank
c_vendor_ytd_amt_rank
rank([{c_vendor_ytd_amt} DESC],[{c_vendor_rn}],PRE_FILTER)
Step 7 - Create calculated field for the grand total of all vendors
c_vendors_ytd_total
sumOver(amt,[truncDate('YYYY',dt)], PRE_FILTER)
Step 8 - Create a calculated field for your percent of total
c_vendor_percent_of_ytd_total
{c_vendor_ytd_amt}/{c_vendors_ytd_total}
Result:
