I am getting some odd results when using an inter-record function in combination with a Sum that aggregates over a couple of dimensions inside a Rangemax:
rangemax(sum(Total <Month, Name> [Monthly usage]-FieldValue('Inclusive units',1)),0)
I am attaching a sample app with a small amount of data. You will see that I have a bigger table (users by month) that gives correct results without the aggregation in the Sum function:
rangemax(sum([Monthly usage])-FieldValue('Inclusive units',1),0)
The Excel file with data inputs and comparison calculations is also attached. Essentially what the app is trying to do is to select an optimal tariff for each user, based on that user's level of usage over time. There are four tariffs, each with a fixed monthly charge ("Rental"), a number of inclusive units and a per-unit charge for usage beyond the inclusive amount. The lowest-cost tariff over three months of data is selected for each user. The values for each tariff can be changed by the user and the tariff assignments are recalculated accordingly. There is no common field in the two tables (tariffs and users), hence the need to use inter-record functions.
The anomaly is that whilst most of the values calculated in the Table: by user are correct there are several incorrect values, as highlighted in red in the Excel sheet. They occur in the calculation of "overage" - i.e. the number of units in excess of the inclusive amount in the tariff.
I am looking for suggestions of a better way of achieving the required result that calculates correctly.
Thanks in advance for your help!
