Have you ever needed to calculate the max value of multiple measures or multiple columns? If you do some googling, you might find solutions talking about nested if statements. Without even going into how you would do this, let me save you some time and explain the “proper” solution. What we’re going to do instead is to create a single-column table and then take the max of the column. For example, if I have 3 columns and a measure titled “Revenue”, “LY Revenue”, “Budget”, [Measure1], respectively, then the code to get the maximum value of the four options is as follows:
Max Value = VAR ValueTable = UNION( ROW(“Value”,SUM(Data[Revenue])), ROW(“Value”,SUM(Data[Revenue])), ROW(“Value”,SUM(Data[Revenue])), ROW(“Value”,[Measure1]) ) RETURN MAXX(ValueTable,[Value])
Explanation
Since aggregation functions (like MAX, MIN, SUM, etc.) only work over columns, it is impossible to say something like MAX(Column1,Column2). Because of this fact, we want to put all of our values into a single column and then use the MAXX function. The UNION function basically pastes together our ROW values, providing us with a nice single-column table to perform our aggregation 😊
3 Comments
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Hello Parker. Thanks for the clear instructions !
Could also show how to do a sum instead of doing a Max for the Total ? Thanks in advance !
Thank you for the explanation! I’m wondering if you added an additional column to your table that represented a name, could you return the name of the lowest value? In the table represented below, It works great to get the lowest value, but I want to return the “Measure” name based on the lowest value of “Value”.
Lowest Measure Value =
VAR ValueTable =
UNION(
ROW(“Value”, [DRG Accuracy %], “Measure”, “DRG”),
ROW(“Value”,[APC Accuracy %], “Measure”, “APC”),
ROW(“Value”, [Principal DX Accuracy %], “Measure”, “Principal DX”),
ROW(“Value”, [Diagnosis Accuracy %], “Measure”, “Diagnosis”),
ROW(“Value”, [ICD Procedures Accuracy %], “Measure”, “ICD Procedures”),
ROW(“Value”, [CPT Procedures Accuracy %], “Measure”, “CPT Procedures”),
ROW(“Value”, [E/M Levels Accuracy %], “Measure”, “E/M Levels”),
ROW(“Value”, [Modifier Usage Accuracy %], “Measure”, “Modifier Usage”),
ROW(“Value”, [Charge Entry Accuracy %], “Measure”, “Charge Entry”),
ROW(“Value”, [Discharge Disposition Accuracy %], “Measure”, “Discharge Disposition”),
ROW(“Value”, [Abstracting Accuracy %], “Measure”, “Abstracting”),
ROW(“Value”, [POA Accuracy %], “Measure”, “POA”)
)
RETURN
MINX(ValueTable, [Value])
Consider i am finding for MIN function. One column has blank and the other has a value say 7. What if I want to consider the blanks as 0 and get my result for MIN as 0? how do I tweak your code in that case?