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...
Need Help
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,