Forum Discussion
Solved
Condition statement with group by
I have two columns in my table: Column 1 ID 34.52 1 50.20 1 24.21 1 31.53 1 27.78 1 20.00 2 31.64 2 16.00 2 -29.41 2 26.74 2 21.23 3 -13.17 3 ...
You can use sum, average, min, max. Since there is only the one value they will all return the same value (as long as column 1 is in the table)
MarkS
Resolver IV
Resolver IVHi yadavu,
Here is a measure that will do what you requested:
Measure 2 =
IF (
CALCULATE ( AVERAGE ( Table1[Column 1] ), ALL ( Table1[Column 1] ) )
> SUM ( Table1[Column 1] ),
0,
SUM ( Table1[Column 1] )
)and here is a more useful measure that will show the value from column1 when it is greater than the average:
OverAverage =
IF (
CALCULATE ( AVERAGE ( Table1[Column 1] ), ALL ( Table1[Column 1] ) )
> SUM ( Table1[Column 1] ),
0,
SUM ( Table1[Column 1] )
)Hi MarkS,
Thank you for the response. This solution did not take into account the grouping of data by the ID column.
- MarkS
Hi yadavu,
Sorry, it looks like I copied the same formula twice in the response, here is the corrected formula
Measure = IF ( CALCULATE ( AVERAGE ( Table1[Column 1] ), ALL ( Table1[Column 1] ) ) < 0, 0, AVERAGE(Table1[Column 1]) )although it does take into account the grouping by ID. In the example data there is no average by group id that is less than 0 so you only get back the original column.
