cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
truongphan Occasional Visitor
Occasional Visitor

Aggregating by each KPI

Hello Gurus,

 

I am using VitaraChart to display a report as the below picture 2017-09-30_16h57_21.png

Column Last Value: I am using aggregate is Last

Column Sum: I am using aggregate is SUM.

 

The Recruited Headcount column have to be SUM because it is the new value every day.

The Remaining Headcount should be the last value by time.

 

My question is: Can I have a measure to calculate when KPI is Recruited Headcount, the aggregate is SUM 

and when KPI is Remaining Headcount then the aggregate is Last value?

 

Then I can replace those columns by the new measure as expected.

 

Thanks,

Truong

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Aggregating by each KPI

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 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




1 REPLY 1
Super User
Super User

Re: Aggregating by each KPI

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 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!