DAX formula for Matrix table to pull highest and lowest values from a specific column

[Ram_Yerramilli]

Scenario: I have a matrix table that details Products and Releases.

Release columns have numerical values. I need to pull the highest and lowest values of a specific Release and its Product. Can you help me with the DAX formula for Matrix? I discovered that we have DAX formulas for only the table which is not in Matrix format.

Please do the needful. Thank you.

[Adamboer]

I understand that you need a DAX formula for a matrix table to pull the highest and lowest values of a specific release and its product.

To achieve this, you can create two calculated measures using the MAXX and MINX functions. Here is a sample formula that you can modify to fit your specific needs:

Highest Value = MAXX(FILTER('Table', 'Table'[Release] = "Release Name" && 'Table'[Product] = "Product Name"), 'Table'[Value])

Lowest Value = MINX(FILTER('Table', 'Table'[Release] = "Release Name" && 'Table'[Product] = "Product Name"), 'Table'[Value])

In the above formulas, replace "Table" with the name of your table. Also, replace "Release Name" and "Product Name" with the specific release and product you want to find the highest and lowest values for, respectively. Finally, replace "Value" with the name of the column that contains the numerical values.

I hope this helps! Let me know if you have any further questions.

[Alf94]

Hi @Ram_Yerramilli ,

Could you please share some dummy data and an example of the desired output? It would be easier for us to help you.

Best,

[Ram_Yerramilli]

Here is the sample data and the example of the desired output in a text box.

[Alf94]

I am sorry but I do not really understand how you get -68 as min value. It is neither the min value for the row "AUDI" nor the min value for the column "Sum of R4". Could you please developp a bit more your idea? Thanks

[Ram_Yerramilli]

It's my bad. I have just added some random values. Please consider the values as the least negative and highest positive numbers.

[Alf94 · Accepted solution]

@Ram_Yerramilli, here is a solution to get the least negative and highest positive values for a specific release:

Min value R4 = 
    MINX(
        ADDCOLUMNS(
            SUMMARIZE(
                'Table',
                'Table'[Brand]
            ),
            "@r4", [R4 sum]
        ),
        MIN( [@r4] )
    )

Max value R4 = 
    MAXX(
        ADDCOLUMNS(
            SUMMARIZE(
                'Table',
                'Table'[Brand]
            ),
            "@r4", [R4 sum]
        ),
        MAX( [@r4] )
    )

Both measures use the [R4 sum] measure defined as:

R4 sum = CALCULATE( SUM( 'Table'[R4] ) )

To get the brands associated to theses values, I wrote the following:

Brand Min Value R4 = 
    var _table =
        ADDCOLUMNS(
            SUMMARIZE(
                'Table',
                'Table'[Brand]
            ),
            "@r4", [R4 sum]
        )
    var _min = MINX ( _table, [@r4] )
    RETURN 
        CALCULATE(
            MIN( 'Table'[Brand] ),
            FILTER(
                _table,
                [@r4] = _min
            )
        )

Brand Max Value R4 = 
    var _table =
        ADDCOLUMNS(
            SUMMARIZE(
                'Table',
                'Table'[Brand]
            ),
            "@r4", [R4 sum]
        )
    var _max = MAXX ( _table, [@r4] )
    RETURN 
        CALCULATE(
            MAX( 'Table'[Brand] ),
            FILTER(
                _table,
                [@r4] = _max
            )
        )

Here is the final result (with random numbers):

If this works, please mark my answer as a solution to help people with the same question as you in the future.

Best,

[Ram_Yerramilli]

Awesome! It's working. Many thanks for sharing the solution 😊