hi @astrontelstar you have a few steps to do.
I am going to make a few assumptions:
- Purchase Order is unique (but it repeats for vendor, client, and profit)
- you have a column for PO
- you have a column for Vendor & Vendor Price
- you have a column for Client & Client Price
- you have a column for Customer Profit (client = customer)
PO | Vendor | Vendor Price | Client | Client Price | Customer Profit
The data will look something like this in a gallery (yours will differ, but the principle is the same)
I collected in a collection colProfit2
Now let's create three collection for you to keep things simple.
col1: colClientTotals
ClearCollect(colClientTotals,AddColumns(GroupBy(colProfit2, "Client", "Clients"),"Clients's Total Price", Sum(Clients,'Client Price')))
col2: colVendorTotals
ClearCollect(colVendorTotals,AddColumns(GroupBy(colProfit2, "Vendor", "Vendors"),"Vendor's Total Price", Sum(Vendors,'Vendor Price')))
col3: colCustomerProfits
ClearCollect(colCustomerProfits,AddColumns(GroupBy(colProfit2, "Client", "Profits"),"Clients's Total Profit", Sum(Profits,'Customer Profit')))
add 3 galleries with 2 labels each.
label1: descriptor
label2: total
Gallery to show clients, i created 4 distinct clients
Gallery Item = colClientTotals
Gallery Item = colVendorTotals
Gallery Item = colCustomerProfits
To rank them, let ssee the top 3 for all (the collection is sorted in descending order so the first 3 entries are what we lookng for.
Top 3 Client Prices
FirstN(Sort(colClientTotals,'Clients''s Total Price',Descending),3)
Top 3 Vendor Prices
FirstN(Sort(colVendorTotals,'Vendors''s Total Price',Descending),3)
Top 3 customer profits
FirstN(Sort(colCustomerProfits,"Clients's Total Profit",Descending),3)
Hope this helps
