cancel
Showing results for
Search instead for
Did you mean:
Helper V

## Measures to extract Max and Min values from Columns and Rows displayed

I look at a lot of conditionally formatted 2D arrays of data.

From this 2D array of data, I would like to create a measure that will give me the min and max values taking into account any filters I have applied to the data.  Each cell is the sum of it's contents.

For example in the screen capture below, i would like to view the min and max of the 2D array of data below with a min measure and max measure:

I have a sample of the data above contained in the excel file with a couple measures that get me close!

https://drive.google.com/open?id=1o0hLwe7o9VJZBkWlzkPF4WDJgN7y7wWb

The measures get me single min and max from a designated vref row, but I would like to look at the whole array of values and get min and max of the displayed values.

I'm close but not close enough...

Thank you...

1 ACCEPTED SOLUTION
Helper V

I finally figured it out:

To get the min or max value for the 2D array of values:

For the max value:

=MAXX (
FILTER (
VALUES ( raw_data[CDC (Coarse+Fine)] ),
raw_data[CDC (Coarse+Fine)]
),
CALCULATE ( SUM ( raw_data[Value] ) ))

For the min value:

=MINX (
FILTER (
VALUES ( raw_data[CDC (Coarse+Fine)] ),
raw_data[CDC (Coarse+Fine)]
),
CALCULATE ( SUM ( raw_data[Value] ) ))

2 REPLIES 2

How about just write the measures (min and max) and put them in a cell (either another pivot table or cube formula). Then use conditional formatting over your pivot table to highlight  the values

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Helper V

I finally figured it out:

To get the min or max value for the 2D array of values:

For the max value:

=MAXX (
FILTER (
VALUES ( raw_data[CDC (Coarse+Fine)] ),
raw_data[CDC (Coarse+Fine)]
),
CALCULATE ( SUM ( raw_data[Value] ) ))

For the min value:

=MINX (
FILTER (
VALUES ( raw_data[CDC (Coarse+Fine)] ),
raw_data[CDC (Coarse+Fine)]
),
CALCULATE ( SUM ( raw_data[Value] ) ))

## Helpful resources

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors