Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

How to find the maximum of a measured value from a SQL server

Hello,

I'm trying to find a way to display the best historical preformance for some company metrics. The data is from a SQL server and comes in two parts. A numerator value and a denominator value. However, there are multiple values per work day and in order to be accurate, the numerators and denominators from a specific work day must first be summed before being divided. 

 

Currently, I am able to display the data by creating a relationship to a table that is a list of individual work days so by creating a 1:Many relationship I can do the calculation using a measure (shown below)

 

AHS SHM CT = divide(sum(AHS_SHM[Cycle_Time_Numerator]),sum(AHS_SHM[Cycle_Time_Denominator]),0)
 
This works to be able to show the value im looking for (called SHM here) for each work day I am interested in. However, to make a good compairison I want to be able to extract the all time maximum value of SHM that willl be able to change when I refresh the data. 
 
The problem I'm running into is that all the functions that find a MAX value need a column of data to do so to. But since the calculation requires a sum I don't know how to use the formula from the measure, when related to a single work day to create a new table that I can find a maximum from. 
 
Is there an easier way to do this that I am missing? If not, I would love some help on how to write a solution.
 
Thanks!
1 ACCEPTED SOLUTION
Sahir_Maharaj
Super User
Super User

Hello @Anonymous,

 

1. Create a measure to calculate the numerator and denominator sum for each work day:

 

NumeratorSum = SUM(AHS_SHM[Cycle_Time_Numerator]) DenominatorSum = SUM(AHS_SHM[Cycle_Time_Denominator]) 

 

2. Create a measure to calculate the SHM value using the numerator and denominator sums:

 

AHS SHM CT = DIVIDE([NumeratorSum], [DenominatorSum], 0) 

 

3. Create a measure to find the all-time maximum SHM value:

 

MaxSHM = MAXX(ALL('AHS_SHM'[WorkDay]), [AHS SHM CT]) 

 

Let me know if you might need further assistance.


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 13K+ 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

1 REPLY 1
Sahir_Maharaj
Super User
Super User

Hello @Anonymous,

 

1. Create a measure to calculate the numerator and denominator sum for each work day:

 

NumeratorSum = SUM(AHS_SHM[Cycle_Time_Numerator]) DenominatorSum = SUM(AHS_SHM[Cycle_Time_Denominator]) 

 

2. Create a measure to calculate the SHM value using the numerator and denominator sums:

 

AHS SHM CT = DIVIDE([NumeratorSum], [DenominatorSum], 0) 

 

3. Create a measure to find the all-time maximum SHM value:

 

MaxSHM = MAXX(ALL('AHS_SHM'[WorkDay]), [AHS SHM CT]) 

 

Let me know if you might need further assistance.


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 13K+ 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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors