Need help with a calculated column

[Anonymous]

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!

Date	ID	Value	Expected result
23/11/2017	A21vc	0	2
23/11/2017	B3gav	1
23/11/2017	J6gfb	1
23/11/2017	G5612	0
24/112017	A21vc	1	2
24/112017	B3gav	0
24/112017	J6gfb	1
25/11/2017	A21vc	0	2
25/11/2017	B3gav	0
25/11/2017	J6gfb	1
25/11/2017	G5612	1

Regards,

[Zubair_Muhammad · Accepted solution]

HI @Anonymous

See the attached PBIX file

I just changed

(sum('Fact Vacancy_Indvi'[Addition]))

to

(sum('Fact Vacancy_Indvi'[Mes]))

[Zubair_Muhammad]

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]

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,

[Zubair_Muhammad]

Hi @Anonymous

It worked with the first sample you provied

See the attached file

Could you share the file?

[Anonymous]

Hi Zubair_Muhammad ,

Below is the link for the sample dataset , kindly let me know if you need anything else

https://drive.google.com/open?id=148r_DPdsPz9aZACA4CcFr70MnpD5VUvl

Thanks,

[Zubair_Muhammad · Accepted solution]

HI @Anonymous

See the attached PBIX file

I just changed

(sum('Fact Vacancy_Indvi'[Addition]))

to

(sum('Fact Vacancy_Indvi'[Mes]))

[Zubair_Muhammad · Accepted solution]

[Anonymous]

Thanks Zubair_Muhammad , that worked:)