Skip to main content
cancel
Showing results for 
Search instead 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

Reply
sdg1393
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
Sahir_Maharaj
Super User
Super User

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

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
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

View solution in original post

3 REPLIES 3
Sahir_Maharaj
Super User
Super User

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

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
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

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

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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.