Difficulty with Lines & Curves in Scatter Plot ---- Any Suggestions?
I have a challenge for the experts ;-)
I'm attaching a DXP file with embedded data.
This is a mock-up of a more complex real analysis.
I am also attaching the accompanying Excel file. It has manual calculations of averages, which is the core problem of this posting (keep reading please).
The data table is very simple, it contains information about sales (local and international) of different models of equipment.
I added 2 calculated columns to the table:
- one is used to get the Revenue per Unit sold (it divides the $ of revenues by the number of units sold)
- the other one calculates the Production Cost per Unit (it divides $ of production cost by the number of units sold)
The Analysis: Showing Proper Averages
If you spend some minutes examining my "SUMMARY" cross table and the two Combination Charts, you will notice that in them I am able to PROPERLY calculate the "Average Revenue per Unit" and the "Average Production Cost per Unit".
The nice thing is that I can use the filters to show, for example, only "International" sales, and the averages are properly recalculated and even shown as a red line in the combination charts.
I can even select a few models (instead of all of them) and the averages are recalculated for that particular group of models.
My problem: The Scatter Plot
The scatter shows the 2 calculated columns from the data table: Revenue per Unit on the Y axis and Production Cost per Unit on the X axis.
I have included in the scatter one horizontal average line and one vertical average line. These are simple averages, so they are wrong because they don't take into account the number of units sold of each equipment model.
I don't seem to find a way of showing the proper averages for Y and X.
I tried adding (in the Lines & Curves property of the scatter) a "Curve from Data Table". I entered a calculation to find the proper average, but it doesn't work.
I left the curve there, but inactive. Try activating it and you will see that a warning is displayed by the title of the scatter plot: "the expression is not valid"
IS THERE ANY WAY OF CALCULATING AND SHOWING THE PROPER AVERAGE IN THE SCATTER PLOT, USING THE "LINES & CURVES"?
OR, IS THERE A SOLUTION USING A SCRIPT?
(In the meantime, I am "manually" solving my problem by adding a horizontal & vertical reference lines to the scatter plot with the proper average values: I look at the "SUMMARY" cross table and then go to the scatter plot properties, Lines & Curves --add, horizontal/vertical line, straight line, fixed value---- This way I can use the chart in a presentation but I have to manually enter a different value each time I change the filters)
Thanks in advance for your thoughts on this issue.