Average Price Differential

[Candace Hays]

To Whom It May Concern,

We are trying to calculate the average price differential in a text box calculated value field when marking specific months in a bar chart.

We have two tables:

1. Revenue and Volumes tables

2. Spot Price Table

We are summing the net revenue and volumes by month, calculating a realized price by month, and comparing the monthly realized price by the monthly spot price. And then calculating an average price differential.

In spotifre, we created a bar chart with monthly price differentials. We tried calculatingin a text box the average price differential, but we are not getting the same answer as we do in the picture below. Also, we would like to calculate the average price when selecting certain months in the bar chart. Is there away to calculate the average price differential Attached is the excel ss and spotfire project.

[Gaia Paolini]

Thank you for uploading the data and dxp.

The problem with your calculated value is that in excel you are taking the average of averages, in Spotfire you are taking the average overall, and the two are different. To check this, you can temporarily remove the x-axis on the bar chart and the height of the single remainingbar will be 0.837.

The simple way would be to show the average line directly on the bar chart. (I am not quite sure if you can show more decimal figures though).

Otherwise, I managed to display it in the calculated value with a few steps (you go through similar steps in the excel file)

1) create calculated column Price Differential for each [service] as

Sum([Gas_Net_Revenue]) OVER ([service]) / Sum([Gas_Net_Volume]) OVER ([service]) / [Gas Spot Price]

Noticing that [Gas Spot Price] is already a single value per [service].

2) create a copy of the Data table, and Pivot it so it has one row per [service] and one column, the [Price Differential]

3) add the calculated value as the average of [Price Differential] over this pivot table.

In order for the pivot table to react to filtering the original table, you need to create a relation between Data and Data Pivot via [service], and make sure that when filtering Data, Data Pivot is set to Include Filtered Rows Only' with respect to Data. You can do it by visualising the Filter Panel, locating the filters for Data Pivot and clicking on the double-grid icon on the right.

[Candace Hays]

Gaia,

 

Thank you for the information.  Sometimes we need to filter on a specific set of wells.  When I filter on a grouping of wells, the bar chart changes and gives me the correct value but the Average Price differential does not.  Is there away to select specific wells  In the attached spreadsheet, Pivot tab, the pivot table is filtered to wellnames, location 10, 100, and 101.

 

 

 

[Gaia Paolini]

Unfortunately calculated columns do not react to filters, that was not part of the question so I did not know. Your best solution is to use the label of the average line on the barchart. By the way I remembered that you can change the number of significant figures by going into Label and Tooltip of the line in question, select Value(y) and click on the Format button on the right.

[Candace Hays]

Thank you Gaia.