PU Funtions Count Vendors with Criterias

Dear team
Our Objective is to obtain per Company Code , the number of vendors which net order value in a given year-month is greater than certain value for PO a particular type of PO

in the example below , Year=2019, PO Type ZLV, Amount 500K

The KPI below is our best last attempt, Still Inaccurate…

KPI 2019:

  1. SUM ( CASE WHEN
  2. PU_SUM(“LFA1”, “EKPO”.“NETWR_CONVERTED”,“EKKO”.“BSART” = ‘ZLV’ AND “EKKO”.“AEDAT_YEAR”=2019) > 500000.00 AND “EKKO”.“AEDAT_YEAR”=2019 AND “EKKO”.“BSART” = ‘ZLV’
  3. then 1.0/PU_COUNT(“LFA1”, “EKPO”.“EBELN”,“EKKO”.“BSART” = ‘ZLV’ AND “EKKO”.“AEDAT_YEAR”=2019)
  4. ELSE
  5. 0.0
  6. END)

My Understanding is that the problem resides in the way we designed the KPI particularly in the Denominator used to obtain the Vendors instead of the cases. if you take a look in Line 3, we are considering all the cases that meet the criteria, except for the condition > 500.000,00 ( I did not include this conditions because I don’t know how) this is why the KPI is approximately to the value, in the example below, outcome is 10.09 which Rounded is 10 vendors when in reality there are 11 vendors ( table)

1- Can you please advise how to adjust the formula or a better solution to achieve our objective?
2- is there a more efficient way to calculate line 2?
The only way we found is checking the conditions twice, inside the PU and Outside the PU function

Thanks a lot for your great Support,
Best Regards,
Cristian

Hi Cristian,
Thanks for reaching out!
When using the Company Code as a dimension, I would suggest to adjust the KPI in the following way:
COUNT(
DISTINCT CASE WHEN
PU_SUM(DOMAIN_TABLE(“EKKO”.“BUKRS”,“EKKO”.“LIFNR” ) – aggregates the value per company code and vendor
, “EKPO”.“NETWR_CONVERTED” – column used for aggregation
,“EKKO”.“BSART” = ‘ZLV’ AND “EKKO”.“AEDAT_YEAR”= 2019) – filter conditions
> 500000.00 – only select vendors with the value greater than the threshold
THEN “EKKO”.“LIFNR” – counts the distinct # vendors
END
)

I hope this formula and the explanation help solving your problem.

Viana