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" )
Super UserHello 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"
)
Thanks for your help, I applied the logic and it works for me , need to do a data validation though. Could you please help me on another item on this -
I have the matrix shown in Red, Green, Yellow color.Now I need a slicer that will show Red, Green and Yellow. If I select Red , the matrix will show only data with Red color and ither cell value will be grey out/ not visible, if I select Green it will show data for green, if I select both Red and Green only those colors data will be visible , rest of the data will be greyed out. Since the color indocator is a measure I can't use it as a slicer so I create a color table and wanted to create a measure that will decide based on the above selection.
could you please help how to achieve on this? Thanks again for your valuable inputs