A question of aggregation...

smiley123

New Member
Joined
Feb 8, 2019
Messages
2
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!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 8, 2019
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,162
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top