calculate average of category averages

[bsz412]

Hello, 

 I would like to create calculated columns:

- averages per category (per supplier per country and per etim class) - this I can calculate.

- Where I got stuck is how to calculate the average of those values above, taken every supplier, country and etim class combination only once.

Example: 

supplier_avg_co2_total: e.g for ABB, for MCB in France: (8+2+9+28)/4 - I can calculate this.

ETIM_country_avg: 

that would show the average of supplier’s averages. In the example below  for France and MCB: (11.75+6.67+14.16)/3

thank you!

[amitchandak]

@bsz412 ,  Try a measure like

AverageX(Summarize(Table, Table[Country], Table[ETIM_country]), calculate([Suppier_Avg_col2_total], allexcept(Table, Table[Country], Table[ETIM_country])))

in case Suppier_Avg_col2_total is a column

AverageX(Summarize(Table, Table[Country], Table[ETIM_country]), calculate(Average([Suppier_Avg_col2_total]), allexcept(Table, Table[Country], Table[ETIM_country])))

[bsz412]

hello @amitchandak ,

Thanks for your help! The DAX gives me this result: 

same number for each row, probably the dax needs a bit fine-tuning, but I dont know how...

[bsz412 · Accepted solution]

This DAX works: 

CALCULATE(

AVERAGEX(

SUMMARIZE('table', 'table'[Country],'table'[ETIM Class],'table'[supplier_avg_CO2_total]),'table'[supplier_avg_CO2_total]

),

ALLEXCEPT('table','table'[Country],'table'[ETIM Class])

)