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

[Ben_Beaudry]

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!

[Sahir_Maharaj · Accepted solution]

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.