How to create an average line in a bar chart?

[lalasisi]

I'm trying to create a dynamic average line in a bar chart on Sisense (orange line).

My sample data looks like the below. Is there a way for Sisense to calculate the average of all teams by default (i.e. $305K) and then display the orange line on the bar chart? In addition, if the user only filters to 2 teams e.g. Boston Red Sox and New York Yankees, the average would be changed to $135K and the line would automatically changed to $135K?

Attached is my sample data and chart in excel

[Angelina_QBeeQ · Accepted solution]

Hi @lalasisi ,

You can add aggregation by teams like in the formula: 

AVG([Condition],sum([Quantity]))

Just take you base formula (sum([Quantity]) - in my case) and put it inside AVG([Condition],formula). 
And replace [Condition] with the column with teams.

More about aggregation you can find here: https://docs.sisense.com/main/SisenseLinux/build-formulas.htm

Always here to help,
Angelina from QBeeQ
[email protected]
QBeeQ - Gold Implementation and Development Partner

[Helena_qbeeq]

Hi @lalasisi ,

You can achieve this by creating a line chart, adding an additional measure that will calculate the average, and changing the chart type for the Cost to column.

Here is the formula to get the average:

And the final result:

Please let me know if this works for you : )

Always here to help,
Helena from QBeeQ
[email protected]
QBeeQ - Gold Implementation and Development Partner

[lalasisi]

Thanks for your reply! It's in the right direction but the formula you shown isn't correct. I followed the documentation to create the correct formula and it worked now. https://docs.sisense.com/main/SisenseLinux/build-formulas.htm

[Angelina_QBeeQ · Accepted solution]

Hi @lalasisi ,

You can add aggregation by teams like in the formula: 

AVG([Condition],sum([Quantity]))

Just take you base formula (sum([Quantity]) - in my case) and put it inside AVG([Condition],formula). 
And replace [Condition] with the column with teams.

More about aggregation you can find here: https://docs.sisense.com/main/SisenseLinux/build-formulas.htm

Always here to help,
Angelina from QBeeQ
[email protected]
QBeeQ - Gold Implementation and Development Partner

[lalasisi · Accepted solution]

Thanks for your reply! It's in the right direction but the formula you shown isn't correct. I followed the documentation to create the correct formula and it worked now.