Monthly Price Impact Analysis with multiple rates per customer ID

Question

Hello, hoping for a solution with the below

I am doing a monthly price analysis, measuring price increase and decreases by customer ID, and by line description.

However some line descriptions have more than one rate, and these rates can be vastly different, and are not always invoiced in the same period.

I would want to sum the customer ID and description by month and also sum the quantity, then divide that to get the rate per month, however these multiple rates lines cause big fluctuations in some months.

I want to create a ranking of where there are multiple rates in the month, e.g. rank 1 is lowest, rank 2 is med etc. Then I will use a range of 20% higher and lower for each ranking. That way I can categorise these rates.

I'm stuck, as I cannot figure out how to bring the ranking back to the data.

Confusing? Sample attached.

Price Analysis.yxmd

adamweaver39 · Accepted Answer

Hey @Scarbrain, sorry, I think I jumped the gun -- I am wondering if the workflow can be refined through using the grouping function on your Multi-Row formula. See attached workflow and let me know if those results look correct.

Scarbrain · Accepted Answer

@adamweaver39  thanks for sending through. Although it wasn't the solution, the sort and multi-row tool was all I needed to do which you alluded to, so appreciate you having a crack!!

Here is the workflow, now I can do price increase analysis without the 3 different price points impacting the results.