cancel
Showing results for
Did you mean:
Helper III

## calculate average of category averages

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!

1 ACCEPTED SOLUTION
Helper III

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])
)
3 REPLIES 3
Super User

@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])))

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!
Helper III

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...

Helper III

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])
)

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors