KPI logic

[avanrijn2]

Dear Sir,

I set up the following KPI indicator with some code posted by Guy in a Cube (Patrick).

KPI Color =
VAR _red = "#FF0000"
VAR _yellow = "#D7DF01"
VAR _green = "#00FF00"
VAR _kpistatus = [KPI Status]
VAR _color =
SWITCH ( _kpistatus, -1, _red, 1, _green, 0, _yellow )
VAR _thisyear =
IF ( ISBLANK ( [#Ordernummer] ), FALSE, TRUE )

RETURN
IF ( _thisyear, _color, BLANK () )

KPI Status =
VAR _bottomthreshold = 00.0064
VAR _topthreshold = 00.0067
RETURN
SWITCH (
TRUE (),
[#Perc] < _bottomthreshold, -1,
[#Perc] > _topthreshold, 1,
[#Perc] >= _bottomthreshold
&& [#Perc] <= _topthreshold, 0
)

KPI Indicator =
VAR _uparrow =
UNICHAR ( 128667 )
VAR _downarrow =
UNICHAR ( 128666 )
VAR _bothways =
UNICHAR ( 128653 )
VAR _kpistatus = [KPI Status]
VAR _icon =
SWITCH ( _kpistatus, -1, _downarrow, 1, _uparrow, 0, _bothways )
VAR _thisyear =
IF ( ISBLANK ( [#Ordernummer] ), FALSE, TRUE )

RETURN
IF ( _thisyear , _icon, BLANK () )

And this is the result:

But as you can see in the table I made here, I have different thresholds for every item in the list,
so BOOKINDIFF is a kpi, DELAY is a kpi etc. with there own Targets to handle the conditional formatting.

Is there a way to iterate through this table to calculate the kpi status for every item ?

Thanx in advance for shining a light on this matter 😃.

Regards,
Arthur

[v-cazheng-msft · Accepted solution]

Hi @avanrijn2 

I'm not sure what you really need. According to the sample data you provide, you can make some changes to your Measures.

KPI Status =

VAR threshold =

    SELECTEDVALUE ( 'Table'[Target] )

RETURN

SWITCH ( TRUE (), [#Perc] > threshold, 1, [#Perc] <= threshold, 0 )

KPI Color =

VAR _red = "#FF0000"

VAR _green = "#00FF00"

VAR _kpistatus = [KPI Status]

VAR _color =

    SWITCH ( _kpistatus, 1, _red, 0, _green )

RETURN

    _color

The result looks like this:

For more details, you can refer the attached pbix.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@avanrijn2 , You need base KPI Status, KPI Indicator should be based on measure using column Target KPI Card or Target

Example

Switch( True() ,
[Percent] =1 , "Green"
[percent] <.75 , "red",
[percent] <.99 , "Yellow"
)

[avanrijn2]

Thank you sir for your quick response. I do not understand your response. The KPI status is allready using something like you are suggesting:
KPI Status =
VAR _bottomthreshold = 00.0064
VAR _topthreshold = 00.0067
RETURN
SWITCH (
TRUE (),
[#Perc] < _bottomthreshold, -1,
[#Perc] > _topthreshold, 1,
[#Perc] >= _bottomthreshold
&& [#Perc] <= _topthreshold, 0

Regards,

Arthur