cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
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
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
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
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning