POWER BI DAX Query for DISTINCT ROWS in A GROUP to get the MAX of a GROUP where the key is not

[avidthinker]

Wondering if anyone can assist

Have a table with values of keys, count and value 

Need to group by Key and determine the distinct count and get the max value for each

Once done, except the the highest Max and if 0, determine the average of all values above based on the Key

Wondering how to come up with a DAX Query for this in POWER BI

[v-kkf-msft]

Hi @avidthinker ,

Please try the measure.

Measure = 
VAR maxvalue =
    CALCULATE ( MAX ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Key] ) )
VAR maxvalue_count =
    CALCULATE ( MIN ( 'Table'[Count] ), ALL ( 'Table' ), 'Table'[Value] = maxvalue )
RETURN
    CALCULATE (
        AVERAGE ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Key] = MAX ( 'Table'[Key] )
                && 'Table'[Value] <> 0
                && 'Table'[Count] <> maxvalue_count
        )
    )

Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[avidthinker]

Hi v-kkf-msft,

I tried your calculations, but does not seem quite right.

To simplify,this is what is required, ignore Count 0 and Value 0 and only consider for now AAA once correct, need to work out for original with multiple Key Values.

Key	Count	Value
AAA	0	0
AAA	1	1000
AAA	1	1100
AAA	1	1200
AAA	2	2000
AAA	2	2100
AAA	2	2200
AAA	3	3000
AAA	3	3100

For AAA, the count is 1,2 values max 1200,2200

max of 2, max total of 3400

average will be 3400/2=1700

Hope this helps