Show monthly cost for the supplier with lowest total cost (monthly + one off fee)

[marikabi]

hello all,

i would need some help with a pivot table.

I'm creating a benchmark, where for every provider (ONE, TWO, THREE), country  and product type (A, B) I'm calculating the total yearly cost as [12*AVG(MONTHLY_COST*FX)+AVG(INSTALLATION_COST*FX)] . The lowest yearly cost will be the best provider.

The first table is the benchmark with all the details, the second table should be the final output where I want to show for each country and product type what is the total yearly cost, the provider name and the monthly cost. I'm struggling with the monthly cost 😞 any idea how to calculate it in the second table?

Formula for Best Cost EUR =  MIN(AGGR(12*AVG(MONTHLY_COST*FX),COUNTRY, PROVIDER, PRODUCT_TYPE)+AGGR(AVG(INSTALLATION_COST*FX),COUNTRY, PROVIDER, PRODUCT_TYPE))

Formula for Best Provider = FirstSortedValue(PROVIDER,
(AGGR(12*AVG(MONTHLY_COST*FX),COUNTRY, PROVIDER, PRODUCT_TYPE)+AGGR(AVG(INSTALLATION_COST*FX),COUNTRY, PROVIDER, PRODUCT_TYPE)))

I have pricelists in several currencies (hence the fx field) and I need to work with averages, I can't simply use firstsortedvalue because the pricelist is more detailed then my desired output.

thanks in advance :)!