Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
truongphan
New Member

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
MFelix
Super User
Super User

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 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

1 REPLY 1
MFelix
Super User
Super User

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 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.