Forum Discussion
Help needed to identify the threshold
Hello sdg1393,
Can you please try this:
1. Ensure that you have established a relationship between the master table and the summary table based on the "Kpi id" field
2. Calculate the Percent Calculation
PercentCalc = DIVIDE(SUM(Summary[numerator]), SUM(Summary[denominator])) * 1003. Identify whether the KPI is Increasing or Decreasing
KPIType = SELECTEDVALUE(Master[type])4. Create Conditional Formatting Rules
WithinThreshold = SWITCH( TRUE(), [PercentCalc] >= SELECTEDVALUE(Master[threshold lower]) && [PercentCalc] <= SELECTEDVALUE(Master[threshold max]), 1, 0 )5. Create another measure to assign a color based on the KPI type and whether the value is within the threshold range
ColorIndicator = SWITCH( TRUE(), [KPIType] = "Increasing" && [WithinThreshold] = 1, "Green", [KPIType] = "Decreasing" && [WithinThreshold] = 1, "Red", "Yellow" )
Hello sdg1393,
Can you please try this:
1. Ensure that you have established a relationship between the master table and the summary table based on the "Kpi id" field
2. Calculate the Percent Calculation
PercentCalc = DIVIDE(SUM(Summary[numerator]), SUM(Summary[denominator])) * 1003. Identify whether the KPI is Increasing or Decreasing
KPIType = SELECTEDVALUE(Master[type])4. Create Conditional Formatting Rules
WithinThreshold =
SWITCH(
TRUE(),
[PercentCalc] >= SELECTEDVALUE(Master[threshold lower]) && [PercentCalc] <= SELECTEDVALUE(Master[threshold max]), 1,
0
)5. Create another measure to assign a color based on the KPI type and whether the value is within the threshold range
ColorIndicator =
SWITCH(
TRUE(),
[KPIType] = "Increasing" && [WithinThreshold] = 1, "Green",
[KPIType] = "Decreasing" && [WithinThreshold] = 1, "Red",
"Yellow"
)
Sahir_Maharaj Thanks for your response; I will test it out and let you know. In addition, just wanted to know how can I use the colorindicator as Slicer value( Red, Green, Yellow)? If I select Red and yellow then only red and yellow will be visible, Green cell value will not be shown?