trying to consolidate/aggregate Vendor Name into one name, but someone nothing is working. Want to use the Vendor Name with the most paid, ie the $5000 one.
Vendor ID Vendor Name #Payments Total_Paid
2468 Acme 15 $5,000
2468 Acme Co. 8 $1,000
2468 Acme Firm 2 $200
desired output
2468 Acme 25 $6,200
i have tried = [Vendor Name] Where([Total_Paid]=Max([Total_Paid]) In([Vendor ID])) but that does not consolidate.
thanks for your advice
1 Like
What isn’t working? Your formula works for me.
I used the following free-hand SQL to recreate your sample data…
SELECT 2468 AS [Vendor ID],'Acme' AS [Vendor Name],15 AS [Number of Payments],5000 AS [Total Paid]
UNION ALL
SELECT 2468,'Acme Co.',8,1000
UNION ALL
SELECT 2468,'Acme Firm',2,200;
I made sure the Number of Payments and Total Paid objects were “Measures” with an aggregation function of “Sum”.
Here is my variable with your formula.
So when you say that it “does not consolidate” what exactly do you mean? There are quite a few pertinent details missing. Can you provide a screenshot? Are your Number of Payments and Total Paid objects measures with and aggregation function of sum?
Noel
2 Likes
