calculate percentage per category with exception rules

[Anonymous]

Hi all,

I have a report that checks if files were delivered on time (= Timeliness). These files are delivered in batches (multiple files are sent at once). I already have the timeliness calculated for each file and now I want to calculate this per batch. I was successful with this, also in applying the rule that if one file in the batch was late, that the timeliness for this batch is 0% and only if all files of a batch are on time then they are 100%. My problem now is, that it will not calculate an average timeliness on the column "Timeliness (Batch) %" which for now shows in all other cases the right value. I have created another column called "T Batch" to test some formulas. Here it shows an average in the total row, but the batch timeliness is calculated wrong. Mathematically the 50% here is right but if its < 100% it needs to be set to 0%.

Here are my Measures for reference:

Timeliness (Batch) % =

VAR Delayed = [Delayed Deliveries (Batch)]

var Total = [Total Deliveries (Batch)]

var result = IF(ISBLANK(Delayed), 0, IF(Total <> 0, DIVIDE(Delayed, Total)))

RETURN

IF(Total < 1 , BLANK(),

    IF( Delayed > 0 , 0 , 1 - result)

)

T Batch =

VAR Delayed = [Delayed Deliveries (Batch)]

var Total = [Total Deliveries (Batch)]

RETURN

AVERAGEX('1_delivered_files', 1-DIVIDE(Delayed, Total))