I want to classify spend by percentile in a column so I can filter against it. Hoping someone has experience in creating such a column.
We have pay dates (Mondays) and I want to classify all vouchers to be paid that day for vendors as to whether the vendor itself fits under the (say) 30th percentile of spend for that pay cycle.
Sample data
The process is
a) to get the total payment amount of all vouchers paying on April 13th.
b) to get the sum of payments for each vendor
c) rank the vendor payments from least to most expensive
d) get the cumulative payment from the least expensive, and if a vendor total payment when added to the cumulative total is under the 30th percentile to put a "Y" in the percentile column.
So in this example let's say the total payment for April 13th is $5,000 so every vendor total under the 30th percentile adds up to no more than $1,500 starting with the smallest vendor.
Vendor 123 has $180 to be paid. When added to all the other smaller vendor total payments the cumulative total is $1,499. Each of those vouchers for the vendors in that list will have a "Y" in the Under 30 Pct column.
Vendor 456 with a total of $190 pushes over the 30th percentile and thus doesn't qualify.
Yes, this would be more feasible as a measure, but this flag we're creating is just one of several ways to slice the payments and we'd like to filter. We have about 7 "levels' of vendor payments we'd like to categorize and be able to filter by levels to see what payment characteristics show up in our pay cycles.
So the total for all vouchers and collecting the sum total for the particular vendor in the row is easy enough, but ranking all the others and then determining the cutoff has me scratching my head.
I'm thinking the process would be to do an ADDCOLUMN with the ranked vendor totals, limit the rows to those vendors under the limit, then test the current row to see if the vendor ID is in the ADDCOLUMN group. But that's beyond my current skill set.
We have pay dates (Mondays) and I want to classify all vouchers to be paid that day for vendors as to whether the vendor itself fits under the (say) 30th percentile of spend for that pay cycle.
Sample data
| Vendor | Voucher | Pay Date | Payment Amount | Under 30 Pct |
123 | A01 | 13-Apr | $50 | Y |
123 | A02 | 13-Apr | $100 | Y |
123 | A03 | 13-Apr | $30 | Y |
456 | B01 | 13-Apr | $100 | |
456 | B01 | 13-Apr | $90 |
The process is
a) to get the total payment amount of all vouchers paying on April 13th.
b) to get the sum of payments for each vendor
c) rank the vendor payments from least to most expensive
d) get the cumulative payment from the least expensive, and if a vendor total payment when added to the cumulative total is under the 30th percentile to put a "Y" in the percentile column.
So in this example let's say the total payment for April 13th is $5,000 so every vendor total under the 30th percentile adds up to no more than $1,500 starting with the smallest vendor.
Vendor 123 has $180 to be paid. When added to all the other smaller vendor total payments the cumulative total is $1,499. Each of those vouchers for the vendors in that list will have a "Y" in the Under 30 Pct column.
Vendor 456 with a total of $190 pushes over the 30th percentile and thus doesn't qualify.
Yes, this would be more feasible as a measure, but this flag we're creating is just one of several ways to slice the payments and we'd like to filter. We have about 7 "levels' of vendor payments we'd like to categorize and be able to filter by levels to see what payment characteristics show up in our pay cycles.
So the total for all vouchers and collecting the sum total for the particular vendor in the row is easy enough, but ranking all the others and then determining the cutoff has me scratching my head.
I'm thinking the process would be to do an ADDCOLUMN with the ranked vendor totals, limit the rows to those vendors under the limit, then test the current row to see if the vendor ID is in the ADDCOLUMN group. But that's beyond my current skill set.
Last edited:
