Forum Discussion
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...
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
- MattAllington
Community Champion
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
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] ) ))
