Forum Discussion

truongphan's avatar
truongphan
New Member
8 years ago
Solved

Aggregating by each KPI

Hello Gurus,   I am using VitaraChart to display a report as the below picture  Column Last Value: I am using aggregate is Last Column Sum: I am using aggregate is SUM.   The Recruited Head...
  • MFelix's avatar
    MFelix
    8 years ago

    Hi truongphan,

     

    You can use the SWITCH function to make this you have to do something like this:

    KPI Value =
SWITCH (
    TRUE (),
    MAX ( KPI_Table[KPIs] ) = "Recruited Headcount", SUM ( KPI_Table[Value] ),
    MAX ( KPI_Table[KPIs] ) = "Remaining Headcount", LASTNONBLANK ( KPI_Table[Value], MAX ( KPI_Table[Date] ) ),
    BLANK ()
)

     

     

    On this formula the only thing I don't know it will work is the MAX ( KPI_Table[Date] ) since I don't know if you have a date column.

     

    Regards,

    MFelix 

     

MFelix's avatar
MFelix
Super User
8 years ago

Hi truongphan,

 

You can use the SWITCH function to make this you have to do something like this:

KPI Value =
SWITCH (
    TRUE (),
    MAX ( KPI_Table[KPIs] ) = "Recruited Headcount", SUM ( KPI_Table[Value] ),
    MAX ( KPI_Table[KPIs] ) = "Remaining Headcount", LASTNONBLANK ( KPI_Table[Value], MAX ( KPI_Table[Date] ) ),
    BLANK ()
)

 

 

On this formula the only thing I don't know it will work is the MAX ( KPI_Table[Date] ) since I don't know if you have a date column.

 

Regards,

MFelix