Forum Discussion

yadavu's avatar
yadavu
New Member
8 years ago
Solved

Condition statement with group by

I have two columns in my table:

 

Column 1ID
34.521
50.201
24.211
31.531
27.781
20.002
31.642
16.002
-29.412
26.742
21.233
-13.173
12.293
5.263
13.263
21.024
2.904
-24.204
8.854
7.594

 

My aim is to create a new measure that takes the same values as "Column 1" if the average of the values in "Column 1" are greater than 0 and takes a value of 0 otherwise. This condition should be grouped by the ID column.

 

How do I add the group by functionality in the statement below?

 

Table 2 = if(Average(Table1[Column 1]) < 0 , 0, Table1[Column 1]) )

  • MarkS's avatar
    MarkS
    8 years ago

    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)

6 Replies

  • MarkS's avatar
    MarkS
    Resolver IV
    8 years ago

    Hi 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] )
)

    • yadavu's avatar
      yadavu
      New Member
      8 years ago

      Hi MarkS,

       

      Thank you for the response. This solution did not take into account the grouping of data by the ID column.

      • MarkS's avatar
        MarkS
        Resolver IV
        8 years ago

        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.

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi yadavu,

     

    You could try this:

    Condition value =
IF (
    CALCULATE ( AVERAGE ( Table11[Column 1] ), ALLEXCEPT ( Table11, Table11[ID] ) )
        > 0,
    CALCULATE ( AVERAGE ( Table11[Column 1] ), ALLEXCEPT ( Table11, Table11[ID] ) ),
    0
)

    Best regards,

    Yuliana Gu