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 :)!

[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 :)!