Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

calculate average

hi everybody,

 i dont know if the title corespond to the request but i need your help.

i want to calculate the averge of month promotion for each employee 

i use distinct it doesnt work.

i want to create a new calculate column that can identify the first line and put 1 if its different from the previous id and blank if the id of row context is similar to the previous context 

Column = if(CALCULATE(min(table[id]),FILTER(table,table[id]<>EARLIER(table[id]))),1,BLANK())

 

Need Help
  • Icey's avatar
    Icey
    7 years ago

    Hi Anonymous ,

    You can create a new calculate column like so:

    1. In Query Editor, add an Index column from 1;

    2. Create columns:

    Column =
VAR PreviousIndex = 'Table'[Index] - 1
RETURN
    'Table'[id]
        - CALCULATE (
            VALUES ( 'Table'[id] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Index] = PreviousIndex )
        )


Column 2 =
IF ( 'Table'[Column] = 1, 1, BLANK () )

     

    Best Regards,

    Icey Zhang

     

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

     

2 Replies

  • Icey's avatar
    Icey
    Community Support
    7 years ago

    Hi Anonymous ,

    You can create a new calculate column like so:

    1. In Query Editor, add an Index column from 1;

    2. Create columns:

    Column =
VAR PreviousIndex = 'Table'[Index] - 1
RETURN
    'Table'[id]
        - CALCULATE (
            VALUES ( 'Table'[id] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Index] = PreviousIndex )
        )


Column 2 =
IF ( 'Table'[Column] = 1, 1, BLANK () )

     

    Best Regards,

    Icey Zhang

     

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

       thank you Icey  its was what i need i appreciate .