Calculate delta between value (price) and average without data action in SAC (SAP Analytics Cloud)

[kvdhom]

Hi,

I have data regarding different material types, which are all sold for different selling prices.
Now I wish to create a table in a story which indicates the selling price for every element sold, and also the delta/difference between this element and the average selling price for the corresponding material type. Note that I thus have multiple averages within the dataset. I do NOT wish to use a data action since I would like to focus on reporting.
Thus, imagine a dataset like this:

The average price material type A is sold for is (500+600+650)/3 = 583.33
Therefore, the delta for item A sold on 01-01-2022 is 500-583.33 = -83.33
I would like to automatically calculate this for every item sold, matching the average corresponding with its material type.

I have so far created a table which calculates the average by Aggregation. I have chosen Aggregation with Account = Price and used OrderID (unique) as the aggregation dimension. This displays the following table.

However, if I adjust the table layout to get each individual selling in a row, the average of course also changes and I cannot subtract the two.

How am I supposed to do this?

[Nverhoeven]

Hi Karlijn, for your use case I'd recommend generating averages with restricted measures and constant selection within the table itself. Me and anna.burkhardt actually did a blog series on the topic which you can find here.

Any further questions feel free to reach out!