cancel
Showing results for
Search instead for
Did you mean:
Anonymous
Not applicable

## How to calculate the sum of the averages in a category?

There are several cities.
There are zons in the cities.
Each section has a step, and for each step there is a goal.

I need to calculate the amount, but not just the amount.

Sum = (average for step 1) + (average for step 2) + (average for step 3) + (average for step 4) + (average for step 5)

Can this be done somehow with the help of a measure, or do you need to create a table where to count it?

 City Zone Step Target City1 1 1 100 City1 1 2 90 City1 1 3 80 City1 1 4 70 City1 1 5 60 City1 2 1 90 City1 2 2 90 City1 2 3 90 City1 2 4 90 City1 2 5 90 City1 3 1 80 City1 3 2 80 City1 3 3 80 City1 3 4 80 City1 3 5 80 City2 1 1 100 City2 1 2 100 City2 1 3 100 City2 1 4 90 City2 1 5 90 City2 2 1 90 City2 2 2 90 City2 2 3 90 City2 2 4 90 City2 2 5 80 City2 3 1 80 City2 3 2 80 City2 3 3 80 City2 3 4 80 City2 3 5 80

I need to display a graph.
Along the axis - cities, and columns above each other. Each bar should show the average step value. I can do this by putting the city on the axis and zone as a series, and then selecting the average function for the data. But I also need to calculate the goal and add a line, but for the goal the series does not work and either the sum of all goals (which does not fit) or the average of the series of ALL goals, which is not correct, is considered.

1 ACCEPTED SOLUTION
Super User

@Anonymous , Try a measure like

sumx(summarize(Table, table[step], "_avg", average(Table[Target])),[_avg])

2 REPLIES 2
Super User

@Anonymous , Try a measure like

sumx(summarize(Table, table[step], "_avg", average(Table[Target])),[_avg])

Anonymous
Not applicable

Yes, looks good. Many thanks for you 😃

## Helpful resources

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors