Forum Discussion

luis_pflucker's avatar
luis_pflucker
Icon for Helper II rankHelper II
2 years ago
Solved

how to summarize one column

Hi Team,

I am trying to categorize a group of items based in at least one value. In the below left table, if at least one of the values on column  "Measure" = KPI Missed, then column "Measure 2" in the right table should show as KPI Missed.

 

Formula used for Measure: 

Measure = if(AVERAGE(accum[BAU])>=AVERAGE(accum[Target]),"KPI Met","KPI Missed")

 

Any suggestion?

Thanks in advance.

Luis

 

Need Help
  • MattAllington's avatar
    MattAllington
    2 years ago

    The exact formula depends on your table structure. What you need to do is cycle through the KPIs and count how many are missed. If the total is at least 1, then by definition it is missed. 
    Something like this

    MEASURE 2=
VAR totalMissed =
    SUMX (
        VALUES ( table[kpi for value realization] ),
        IF ( [measure1] = "KPI Missed", 1 )
    )
RETURN
    IF ( totalMissed >= 1, "KPI missed" )

     

2 Replies

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    2 years ago

    The exact formula depends on your table structure. What you need to do is cycle through the KPIs and count how many are missed. If the total is at least 1, then by definition it is missed. 
    Something like this

    MEASURE 2=
VAR totalMissed =
    SUMX (
        VALUES ( table[kpi for value realization] ),
        IF ( [measure1] = "KPI Missed", 1 )
    )
RETURN
    IF ( totalMissed >= 1, "KPI missed" )

     

    • luis_pflucker's avatar
      luis_pflucker
      Icon for Helper II rankHelper II
      2 years ago

      Thanks a lot Matt!! solution worked 🙂 I just added the false condition when KPI Met, then measure in Measure2 is correct:

       

      Measure 2 = VAR totalMissed =
          SUMX (
              VALUES ( accum[KPI for value Realization]),
              IF ( [Measure] = "KPI Missed", 1 )
          )
      RETURN
          IF ( totalMissed >= 1, "KPI missed" ,"KPI Met")