Help needed to identify the threshold

[sdg1393]

Hi,  I need a design and DAX help for the following requirement -

I have two tables - one is master table with kpi id, category, kpi name and threshold value lower and threshold value upper and type

for example (table structure - kpi id, category, koi name, threshold lower, threahold max, type)

1, Training, completion, 86, 90, Increasing

2, Training, Trend, 65, 85, decreasing

3, Audit, timeliness, 70,80, Increasing

now I have a summary table with month wise value of all KPIs Like kpi id, year, month, numerator, denominator, level1, level2

Kpi id is the key to relation between two tables- 

1, 2023, april, 1, 1, Enterprise, HR

1, 2023, april, 10,10, Enterprise, Finance

2, 2023, april, 4,5, Enterprise, HR

2, 2023,april, 2,5, Enterprise, Finance

I have a slicer for Year , Month and Level1 and Level2

 the requirement is - If I select year, month and Level 1 then for It will calculate [sum numerator / sum denominator]*100 (named as PercentCalc) for each Kpi id and that kpi id will match with kpi master table to get the threshold and compare the of sum value (>=, <=). Here it will be 11/11

if I select year, month, level1 and Level2.. sum will take till level2 for kpi id 2, it will be 4/5 or 2/5 based on level 2 selection

the reason of increasing and decreasing type is if it's increasing and sum calculation value is >0 and <threshold lower then color Red if it's decreasing and sum calculation value is >0 and <threshold lower then it's green. 

I need a pivot table with yearmonth,KPi name, percent cal and background color as Red, Yellow, Green 

need help how to proceed with the approach?

[Sahir_Maharaj · Accepted solution]

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])) * 100

3. 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"
)

[sdg1393]

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

[sdg1393]

@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?