Forum Discussion
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
[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]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.
Ok, to figure out with calculation column based on data from other columns and some constants I suppose you need to use such feature as DAX that is available in Power BI Desktop.
There is a topic where people discussed conditional calculations in DAX for instance.
Unfortunately, DAX is out of our expertise because it is a feature of Desktop, but you can ask questions regarding DAX in the Desktop thread.
Kind Regards,
Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
[email protected]