Sunday, May 14, 2023

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
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog