Forum Discussion
POWER BI DAX Query for DISTINCT ROWS in A GROUP to get the MAX of a GROUP where the key is not
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
2 Replies
v-kkf-msftCommunity Support
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.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
