Add custom line on scatter plot

[christian PONTHUS]

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.

[Fabian Duerr]

Can't you just use Avg([X])

This will only take the data rows into account that you filter to.

 

Please provide some sample data if this is not what you were looking for.

[christian PONTHUS]

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.

 

Thanks

 

Best

[Fabian Duerr]

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.