PowerBI Measure for Max and Min with a column total that respects row level context.

[Rob123]

Hello,

I have a shaft depth of value of: SM4_TASKS[FLT_DEPTHBENCH]. (Example values are 437.5m, 668.2m, 864.9m etc).

I have used the built in grouping function to bin the rows in the SM4_TASKS table into 10m bins. The name of this grouping is: 
SM4_TASKS[FLT_DEPTHBENCH (10m bins)]

I would like to get the Max and Min SM4_TASKS'[FLT_DEPTHBENCH] value within each bin and calculate the delta difference between the two values. Eg: Inside the 460m-470m bin eg, I would expect to find 460.2m (Min) and 469.9m (Max).

I have achieved this successfully with the following:
Delta Depth (m) =
MAX('SM4_TASKS'[FLT_DEPTHBENCH]) - MIN('SM4_TASKS'[FLT_DEPTHBENCH])

The column total of the first table (unfiltered) correctly sums all of the delta depths in the column.

I have a secondary table below that is being filtered when I select a number of bins in the first table, I would like for the column total in the second table to sum the delta values of only the selected bins. In the image you can see in the second table (lower), the column total is 119.8 rather than 18.6 (9.8+9.8). 

How do I adjust my measure so that the column total sums the selected bin delta values.

(I understand the error, that it is taking the MAX Value of the 780 bin and the MIN Value of the 670 bin, and calculating the difference of the two. However I just dont know how to fix!)

(I also have page level filters on which need to be respected).

Many thanks!
Rob

[Rob123]

Thank you OwenAuger. Legend! I used the first solution and this worked perfectly.

[OwenAuger · Accepted solution]

Hi @Rob123 

I think this is what you're looking for:

Delta Depth (m) = 
SUMX (
    VALUES ( SM4_TASKS[FLT_DEPTHBENCH (bins)] ),
    CALCULATE ( MAX ( SM4_TASKS[FLT_DEPTHBENCH] ) - MIN ( SM4_TASKS[FLT_DEPTHBENCH] ) )
)

This iterates over the distinct "bins", summing the delta per bin.

CALCULATE is required to convert the value of the bin column within the row context of VALUES ( ... ) into a filter (context transition).

Alternatively, you could leave your existing Delta Depth (m) measure as-is, and create a new measure referencing it:

Delta Depth (m) Summed per Bin = 
SUMX (
    VALUES ( SM4_TASKS[FLT_DEPTHBENCH (bins)] ),
    [Delta Depth (m)]
)

In this case, CALCULATE is added automatically due to the measure reference.

Do either of these methods work for you?