Scenario1: MAX of 3 Measures:
Suppose we have 3 measures like below, from which we need to find the Max Value.
Printer Sales =
CALCULATE([Net Sales], tbl_Products[Prod_Name]=“Printer”)
Router Sales =
CALCULATE([Net Sales], tbl_Products[Prod_Name]=“Router”)
Laptop Sales =
CALCULATE([Net Sales], tbl_Products[Prod_Name]=“Laptop”)
Now we can find the Max of these 3 Measures as shown below:
Max Value of 3 Measures =
MAXX (
{ [Printer Sales],
[Router Sales],
[Laptop Sales]
}, [Value]
)
We can also find which Product is contributing to Max Sales :
Max Value Product =
FILTER(VALUES(tbl_Products[Prod_Name]),
[Net Sales]=
MAXX (
{ [Printer Sales],
[Router Sales],
[Laptop Sales]
}, [Value] )
)
Result:
Scenario2: MAX of 3 Columns:
Suppose we have sample table called “JustNumbers” with 3 Columns like below, from which we need to find the Max Value.
Max Value of 3 Columns =
MAXX (
{ MAX(JustNumbers[NumCol1]),
MAX(JustNumbers[NumCol2]),
MAX(JustNumbers[NumCol3])
}, [Value]
)
We can also find which Column is contributing to Max Value:
Max Value Column =
VAR vCol1Name = NAMEOF(JustNumbers[NumCol1])
VAR vCol2Name = NAMEOF(JustNumbers[NumCol2])
VAR vCol3Name = NAMEOF(JustNumbers[NumCol2])
VAR vSummary =
UNION (
ROW ( “ColumnName”, vCol1Name,“ColumnValue”, MAX(JustNumbers[NumCol1])),
ROW ( “ColumnName”, vCol2Name,“ColumnValue”, MAX(JustNumbers[NumCol2])),
ROW ( “ColumnName”, vCol3Name,“ColumnValue”, MAX(JustNumbers[NumCol3]))
)
VAR vResult =
CONCATENATEX ( TOPN ( 1, vSummary, [ColumnValue], DESC ), [ColumnName], “, “ )
RETURN
{ vResult }
Result:
——————————————————————————————————–
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
——————————————————————————————————–
Be the first to comment