I'm working on an ask that I'm not sure is possible to meet. I have delivery data that I need to calculate the on time percentage over an adjustable timeframe. My key columns are supplier name (column1), PO lines delivered (column2), and PO lines delivered on-time (column3). I also need to display a column that show the percentage of lines that were delivered on-time (column3/column2). The difficulty I'm running into is that the on-time percentage has to be calculated at a fixed timeframe, so regardless of whether I've built it as a beast mode or in ETL, it's "set" at a week or month or however I aggregated the number of lines. My boss wants the on-time % to adjust and be accurate whether we've quick filtered to a week or a month or a year of data. The second image below shows that this causes duplicate lines for each company since I can aggregate the total lines and the lines on time but not the % on-time.

I've been thinking through this a lot and I believe the problem comes down to the order of operations. The % on-time calculation has to be done at whatever timeframe I set within the beast mode or in ETL prior to aggregation. What I'm being asked to do is perform a division calculation AFTER aggregation.

Does anyone know of a way to do this or a workaround?