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,