Struggling to Find an overall percentage

AUsher2511
AUsher2511 Member
in Magic ETL

Hi, new to domo and in particular beastmode, hoping someone can help.

I have a KPI dashboard consisting of 30 plus customers and i want to find out the percentage of overall customers that meet or overachieve their KPI attainment each month. For example, if I have 10 customers and 5 meet or overachieve kpi then I'd like to see a card showing 50%.

I've been using this formula (SUM(Attained) / MAX(KPI)) * 100 which shows me percentage of KPI attainment at the individual client level, see screenshot attached.

Screenshot 2024-10-09 120515.png

When i try to use the following formula

( COUNT(DISTINCT CASE WHEN SUM(`Attained`) >=MIN(`KPI`) THEN

`Source` END

) /COUNT(DISTINCT `Source`)) *100

I get a an error, can anyone help?

Thanks

Best Answer

  • ColemenWilson
    ColemenWilson Coach
    edited October 9 Answer ✓

    Okay so they would have to hit their attainment for all of their KPI's to count as attained?

    For example:

    Customer A has 2 rows of data. In one row they attained and in one they didn't - overall as a customer have they hit their KPI? Would they be a 0 or a 1 in the numerator?

    Customer B has 2 rows of data. In both they attained. Would they be a 1 in the numerator? A 2 wouldn't make sense to me since they only count once in the denominator. Or would they be a 2 over a 2 in the denominator?

    If I solved your problem, please select "yes" above

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited October 9

    Are there multiple rows per customer? Or a single row per customer?

    If single row per customer, the beastmode would be: 

    SUM(CASE WHEN (`Attained`/`KPI`) >= 1 THEN 1 ELSE 0 END) / COUNT(DISTINCT `Customer ID`)

    If I solved your problem, please select "yes" above

  • AUsher2511
    AUsher2511 Member

    Hi there,

    Multiple rows per customer

  • ColemenWilson
    ColemenWilson Coach
    edited October 9 Answer ✓

    Okay so they would have to hit their attainment for all of their KPI's to count as attained?

    For example:

    Customer A has 2 rows of data. In one row they attained and in one they didn't - overall as a customer have they hit their KPI? Would they be a 0 or a 1 in the numerator?

    Customer B has 2 rows of data. In both they attained. Would they be a 1 in the numerator? A 2 wouldn't make sense to me since they only count once in the denominator. Or would they be a 2 over a 2 in the denominator?

    If I solved your problem, please select "yes" above

Categories