Jump to content

Add custom line on scatter plot

christian PONTHUS

Recommended Posts

Dear All,

I'm having a project table with revenue, gross profit (and many other parameter, such as category, project type...). I made a scatter plot with Revenues in Y and Gross Margin in X, calculated as Sum(Profit)/Sum(Revenues)%. Scatter plot have 1 marker per project. There are thousands. I'd like to add a vertical line for Gross margin that would be actualized according to current filterting (According to project categories, project type. that are selected or not). I cannot find out how to define the Custom expression since the table columns (Gross Profit, Revenues) are not listed in the "Available Columns" box of the Custom Expression dialog box. If I enterin the "Expression" box the formula Sum(Profit)/Sum(Revenues), I've got error message "could not find colums :"reference line.editview.GrossProfit".

The vertical line cannot be a simple average of all Gross Margin markers since they all have different weight (Revenues, Profit)

Any thoughts Thanks a lot.

Link to comment
Share on other sites

Dear fabd,


Thanks a lot.


No, a simple average provides wrong result since it averages the Gross Margin of Markers. But markers are not having the same weight. One can have a gross margin of 40% with 1,000$ revenues and 400$ gross profit, while another one can have a gross margin of 20% with 10,000$ revenues and 2000$ gross profit. Obviously the global GP would be (400+2000)/(1000+10000), not the average of 40% and 20%.


Thus the vertical line should be calculated as the x-axis is: Total sum of GProfit/Total Sum of Revenues for current selection. Not the average of each individual markers Gprofit/Revenues.





Link to comment
Share on other sites

Can you please provide some sample data


I understand where you want to go, but without knowing the structure of the underlying data it is hard to find the solution. I would suggest to replace the expression on the x-axis to Sum([...]*[...]*100) but there might be a good reason why you use Sum([...]) * Sum([...]) *100. Overall, it's not clear why you need a Sum() on the X-axis.


If we can't calculate the value directly in the visualization, we could for sure use a data function to return a document property and then show a line at the value of this document property.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...