How to find the MAX Value of Multiple Measures or Columns in Power BI

How to find the Maximum Value of Multiple Measures or Columns in Power BI

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.

Now we can find the Max Value of these 3 Columns as shown below:

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

--------------------------------------------------------------------------------------------------------