cancel
Showing results for
Did you mean: Regular Visitor

## 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  Community Champion

Hello @Ben_Beaudry,

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
P.S. Want me to build your Power BI solution?
Data Scientist | Data Engineer | Data Analyst | AI Engineer

➤ Website: https://sahirmaharaj.com

➤ Email: sahir@sahirmaharaj.com

➤ Lets connect on LinkedIn: Join my network of 12K+ professionals

➤ 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  Community Champion

Hello @Ben_Beaudry,

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
P.S. Want me to build your Power BI solution?
Data Scientist | Data Engineer | Data Analyst | AI Engineer

➤ Website: https://sahirmaharaj.com

➤ Email: sahir@sahirmaharaj.com

➤ Lets connect on LinkedIn: Join my network of 12K+ professionals

➤ 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   