Calculate ratio of sales document where cancellation reason for items = 0

Hi there!

I have a little problem here with a single KPI. What I have are two tables (Sales Document Header VBAK, Sales Document Item VBAP).
Only the VBAP includes a column with cancellation reasons (which are always the same for one sales document). The offer is still open, if the cancellation reason is null.
However, I don’t know how to calculate the ratio of open offers, but only for the VBAK table/ VBAK.VBELN (just for the headers, not for every item). I could only create a OLAP table with the VBAK.VBELN and the cancellation reasons by clicking on distinct values in the advanced options.
I tried different PU functions to join the tables and the filter ISNULL, but did not succeed…

Any help is highly appreciated. Thanks!

Best regards

I have something similar to your requirement. you can check if this one helps:

AVG(
CASE WHEN
PU_COUNT_DISTINCT(VBAK, “VBAP”.“POSNR”) = PU_COUNT(VBAK, REMAP_VALUES(“VBAP”.“ABGRU”, [’’ , NULL]))
THEN 1.0 – all order line items are rejected
ELSE 0.0 – at least one order line is not rejected/cancelled
END
)

Thanks a lot! That actually helped :slight_smile: