cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Help needed to identify the threshold

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?

1 ACCEPTED SOLUTION
Super User

Hello @sdg1393,

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"
)``````

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
3 REPLIES 3
Super User

Hello @sdg1393,

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"
)``````

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Frequent Visitor

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.

Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors