cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cncnvr Frequent Visitor
Frequent Visitor

Creating KRI dashboard with thresholds in Power KPI Matrix

Hello V-viig and all,

I am a beginner with Power BI and working on creating a Key Risk Indicators (KRI) dashboard. Power KPI Matrix definitely helps with KRIs also.

I reviewed the PowerKPIMatrix 2.1.4 file to understand the basics. The  sample file posted was beneficial. I am trying create my own KRI dashboard by tweaking that sample file instead of creating my own from scratch (I dont have such expertise with the tool yet).

- How can I set different thresholds for different metric names? I think sample file applies same threshold to all KPIs

- What should be my Calc Variance, Calc KPI index colums for below example?

Example

KRI1 - Green status if value is < 2%, Yellow between 2-4%, Red > 4%

KRI2 - Green if >50, Yellow 50-35, Red <35

KRI3 - Green if > 1%, Yellow 1-0.85%, Red< 0.85% 

 

I gave you different scenarios so you can understand that some KRIs are numbers, some percentages and I am trying to display Green, Yellow, Red Status

I hope this question makes sense. Hope to hear back.

 

https://visuals.azureedge.net/app-store/PowerKPIMatrix.2.1.4.0.pbix

 

Sample file Power KPI Matrix File

10 REPLIES 10
v-evelk Senior Member
Senior Member

Re: Creating KRI dashboard with thresholds in Power KPI Matrix

Hello,

 

Sorry for the delay. I am working on your question and will provide some instruction tomorrow.

It will be better also if you sent your report to pbicvsupport@microsoft.com to let me better understand you issue.

 

Thanks!

 

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
pbicvsupport@microsoft.com

v-evelk Senior Member
Senior Member

Re: Creating KRI dashboard with thresholds in Power KPI Matrix

Hello,

 

Could you clarify please what type of data structure do you use, Column-Based or Row-Based?

 

Thanks!

 

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
pbicvsupport@microsoft.com

cncnvr Frequent Visitor
Frequent Visitor

Re: Creating KRI dashboard with thresholds in Power KPI Matrix

Thank you for the reply, just sent you an encrypted email per your request.

v-evelk Senior Member
Senior Member

Re: Creating KRI dashboard with thresholds in Power KPI Matrix

Sorry, I cannot open link from your e-mail.

 

If you user row-based schema you will need to create a 1 new column and fill it by indexes values (0,1,2) calculated for a measure and target value by criteria that you described and then use this field in "KPI Indicator Index" bucket and set up colors and icons for indicator indexes in "KPI Indicator" section.

 

If you use column-based schema you will need to create a 3 new columns, set them in "KPI Indicator Index" bucket and manually map them for measure and comparison value ("Edit" menu item in the top right corner of the visual).

 

Kind Regards,

 

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
pbicvsupport@microsoft.com

cncnvr Frequent Visitor
Frequent Visitor

Re: Creating KRI dashboard with thresholds in Power KPI Matrix

If you user row-based schema you will need to create a 1 new column and fill it by indexes values (0,1,2) calculated for a measure and target value by criteria that you described and then use this field in "KPI Indicator Index" bucket and set up colors and icons for indicator indexes in "KPI Indicator" section. --- This is the part I am trying to do and dont know how to do?

 

I understand you could not open the encrypted email, below is the screenshot of my sample data and what I am trying to do. Let’s do one example for the KRI below. I provide both the threshold (Red-Yellow-Green) and 4 months of sample data:

ALLL/Loans

<0.55%

0.55 - 0.75%

>0.75%

 

 

Metric

Date

Value

ALLL/Loans

12/1/2018

0.45%

ALLL/Loans

11/1/2018

0.80%

ALLL/Loans

10/1/2018

0.67%

ALLL/Loans

9/1/2018

0.92%

 

What should be the formula in my “Calculated KPI Index” column in the sample file? My whole purpose is to modify this sample table to show relevant colors for my values when they are in the respective thresholds I point above.

v-evelk Senior Member
Senior Member

Re: Creating KRI dashboard with thresholds in Power KPI Matrix

I am not sure that I completely understood you, but why you cannot just set index column like following?

 

Metric

Date

Value

IndicatorIndex

Loan1

12/1/2018

0.45%

1

Loan1

11/1/2018

0.80%

3

Loan2

10/1/2018

0.67%

2

Loan2

9/1/2018

0.92%

3

 

If I am mistaken, please will draw and provide a picture what do you want to see.

 

Kind Regard,

 

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
pbicvsupport@microsoft.com

cncnvr Frequent Visitor
Frequent Visitor

Re: Creating KRI dashboard with thresholds in Power KPI Matrix

As you know I want to use the sample file (I posted in my first message) and apply my KRIs to that file.

In that file there is no column or field to enter the thresholds but there is a calculated column called "KPI Indicator Index".

- "KPI Indicator Index" uses a variances from a target value instead of thresholds. This variance is calculated in "Calc Variance" column.

My question is: If I want my KPI table to show Green/Yellow/Red colors based on the thresholds I gave above, What should be the calculation I enter into "KPI Indicator Index" and "Calc Variance" columns?   Because these are the columns that determine what color the indexed values display.

I dont have targets, I have thresholds; which can be used as a target. Does this make sense?

 

Green-Yellow-Red Thresholds (targets)

KRI

<0.55%

0.55 - 0.75%

>0.75%

 

 

Metric

Date

Value

KRI

12/1/2018

0.45%

KRI

11/1/2018

0.80%

KRI

10/1/2018

0.67%

KRI

9/1/2018

0.92%

v-evelk Senior Member
Senior Member

Re: Creating KRI dashboard with thresholds in Power KPI Matrix

You need to use your actual data as a basis for Index calculation and compare it with a threshold.

Let's assume that your actual data value is 0.45% and it is less than your minimal threshold so, you just need to set  Index 1 for such value for instance and set up colors and icon in "KPI Indicator" options group and set "Value" option as 1. The similar calculation you need to do for other rows (Index 2 if actual value in boundaries of 0,55-0.75 and so on).

 

Kind Regards,

 

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
pbicvsupport@microsoft.com

cncnvr Frequent Visitor
Frequent Visitor

Re: Creating KRI dashboard with thresholds in Power KPI Matrix

On high level I understand what you are saying and I also figured formatting the KPI Indicators the way you explained.

The only prtion I am struggling is to writing the calculation and comparing it with threshold part. Can you give me one or two examples based on the data I provided? I mean writing the calculation part that goes into the cell.  We can do one for below and one in the middle like you said.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 164 members 1,988 guests
Please welcome our newest community members: