Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Need help with a calculated column

Hi,

I am looking to group two columns and find the sum of result, as given in the table below.

I hope we can use Summarize() to acheive this , but  that would create a new table and I dont want the result as a new table.  I want to have this column with in the same table as a calculated column, Thanks in advance for your help!

 

DateIDValueExpected result
23/11/2017A21vc02
23/11/2017B3gav1
23/11/2017J6gfb1
23/11/2017G56120
24/112017A21vc12
24/112017B3gav0
24/112017J6gfb1
25/11/2017A21vc02
25/11/2017B3gav0
25/11/2017J6gfb1
25/11/2017G56121

 

Regards,

 

Need Help

7 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi Anonymous

     

    See if this helps.

     

    Do you want the resulting sum only against the first item of that date?

     

    =
VAR Firstitem =
    FIRSTNONBLANK (
        CALCULATETABLE (
            VALUES ( Table1[ID] ),
            FILTER ( ALL ( Table1 ), Table1[Date] = EARLIER ( Table1[Date] ) )
        ),
        Table1[ID]
    )
RETURN
    IF (
        Table1[ID] = Firstitem,
        CALCULATE ( SUM ( Table1[Value] ), ALLEXCEPT ( Table1, Table1[Date] ) ),
        BLANK ()
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks Zubair,

      I have tried your formula, but I get an empty column, below is my screeen shot.

      Kindly let me know if you need any more info

       

      column =
      VAR Firstitem =
      FIRSTNONBLANK (
      CALCULATETABLE (
      VALUES ( 'Fact Vacancy_Indvi'[Did] ),
      FILTER ( ALL ( 'Fact Vacancy_Indvi' ), 'Fact Vacancy_Indvi'[Date] = EARLIER ( 'Fact Vacancy_Indvi'[Date] ) )
      ),
      'Fact Vacancy_Indvi'[Did]
      )
      RETURN
      IF (
      'Fact Vacancy_Indvi'[Did] = Firstitem,
      CALCULATE ( (sum('Fact Vacancy_Indvi'[Addition])) , ALLEXCEPT ( 'Fact Vacancy_Indvi', 'Fact Vacancy_Indvi'[Date] ) ),
      BLANK ()
      )

       

       

       

      Thanks,