A question of aggregation...

[smiley123]

Hi all

Long time lurker, first of many posts hopefully...

I have a table in power bi with 2 columns.

Column 1 = type of animal. Dog, cat, hamster, mouse, etc.
Column 2 = weight of animal. Integer value.

I have a bar chart with the x axis = type of animal. Each bar is the average weight of each type of animal.

I want to add a straight line on the chart which shows the average weight of all the animals regardless of animal type. Sounds simple right... Could anyone help with how I could do that?

Thank you for any help!

 

[Jeffrey Mahoney]

Just put the value in a new column in every row you have data and use that in the chart. Create a new Y using those values.

 

[smiley123]

Finally managed to getting round to looking at this. In the end I created a table with a calculated column using the DAX formula SUMMARIZE..

To apply my real world problem to my example: SUMMARIZE ([original table], "Avg Weight",average(weight of animal))

My real world problem was a little more complicated since I had to summarise by a number of columns as well as having an average that changed depening on when certain report level filters were applied, but hopefully this illustrates the solution.

Any other method didn't return the expected output since I needed one single flat line of the graph that would change based on certain report level parameters, and averaging any other way produced a line that was not flat but that was influenced by the values on the x axis which was NOT what I wanted.