Forum Discussion
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
[email protected]
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.
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
[email protected]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%
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
[email protected]