Nested Aggregations

Dear Celonis Community,
We have a KPI ( KRI-P2P-C01-Amount ) which is a ratio of a SUM ( See formula 1 below) so far , so good.

Now Based on that formula I need to count the number of Company codes (Or selected dimension) that are exceeding a predefine threshold, What I would need something like is:

COUNT ( CASE WHEN KPI("KRI-P2P-C01-Amount") > <%= KRI_Amount_Main_Value_High_Threshold %> THEN 1.0 else NULL END)

Of course this does not work because of nested aggregation, I attempt to use PU_SUM ( See Formula 2 below)but as it work in case level I always ended up doing the SUM of the PU, and afterwards I cannot aggregate it further.

My desired output would be as follows ( based on the screenshot provided below)

  • companies outside threshold :2
  • companies : 8
  • % of companies not compliant with Selected KPI 25%

Can you please advise how to achieve this?

Best Regards,

Cristian

KRI-P2P-C01-Amount

SUM( CASE WHEN
MATCH_ACTIVITIES(EXCLUDING['Delete Purchase Order Item', 'Cancel Post Invoice'])= 1 AND
PROCESS EQUALS 'Clear Invoice' TO ANY TO 'Cancel Goods Receipt' AND
PROCESS NOT EQUALS 'Cancel Goods Receipt' TO ANY TO 'Clear Invoice'

THEN "EKPO"."NETWR_CONVERTED" ELSE 0.0 END)/SUM("EKPO"."NETWR_CONVERTED")

KRI-P2P-C01-Amount_PU

        SUM(PU_SUM ( "EKKO", "EKPO"."NETWR_CONVERTED" , MATCH_ACTIVITIES(EXCLUDING['Delete Purchase Order Item', 'Cancel Post Invoice'])= 1 AND
        PROCESS EQUALS 'Clear Invoice' TO ANY TO 'Cancel Goods Receipt' AND
        PROCESS NOT EQUALS 'Cancel Goods Receipt' TO ANY TO 'Clear Invoice' ))
        /
        SUM(PU_SUM("EKKO","EKPO"."NETWR_CONVERTED"))