SUM based on several conditions

[ShiMaria]

Hello, 

I have the sample of data below. 

What I need is to display the SUM of NNS per YEAR only for the latest gate:

- if Gate3 exsist in the table for specific project, its values for NNS should be SUMmed

- if there is not yet Gate3, but there is Gate2, its values for NNS should be SUMmed

- if there is only Gate1 yet, than its values for NNS should be SUMmed

thank you in advance for the support!
Maria

GRM	DNES	Gate	NNS Year	NNS Value	NNS Year Pure
March 2019	DNES-001	Gate 1	NNS 2019	1018	2019
August 2019	DNES-001	Gate 3	NNS 2019	1045	2019
July 2019	DNES-001	Gate 2	NNS 2019	1045	2019
July 2017	DNES-002	Gate 1	NNS 2019	16965	2019
July 2019	DNES-002	Gate 3	NNS 2019	17844	2019
May 2019	DNES-002	Gate 2	NNS 2019	16965	2019
May 2019	DNES-002	Gate 2	NNS 2019	19758	2019
July 2019	DNES-002	Gate 3	NNS 2019	20782	2019
July 2017	DNES-002	Gate 1	NNS 2019	19758	2019
May 2019	DNES-004	Gate 1	NNS 2020	1726	2020
May 2019	DNES-004	Gate 1	NNS 2021	2222	2021
May 2019	DNES-004	Gate 1	NNS 2022	3278	2022
July 2020	DNES-004	Gate 2	NNS 2021	4409	2021
July 2020	DNES-004	Gate 2	NNS 2022	11631	2022
July 2020	DNES-004	Gate 2	NNS 2023	23194	2023
September 2019	DNES-003	Gate 2	NNS 2020	151968	2020
October 2019	DNES-003	Gate 3	NNS 2020	214173	2020
September 2019	DNES-003	Gate 2	NNS 2020	308776	2020
October 2019	DNES-003	Gate 3	NNS 2020	353687	2020
September 2019	DNES-003	Gate 2	NNS 2020	92605	2020
October 2019	DNES-003	Gate 3	NNS 2020	106305	2020
September 2019	DNES-003	Gate 2	NNS 2020	120967	2020
October 2019	DNES-003	Gate 3	NNS 2020	172694	2020
September 2019	DNES-003	Gate 2	NNS 2020	350572	2020
October 2019	DNES-003	Gate 3	NNS 2020	518773	2020
September 2019	DNES-003	Gate 2	NNS 2020	216831	2020
October 2019	DNES-003	Gate 3	NNS 2020	302131	2020
September 2019	DNES-003	Gate 2	NNS 2020	97180	2020
October 2019	DNES-003	Gate 3	NNS 2020	104958	2020
September 2019	DNES-003	Gate 2	NNS 2020	116000	2020
October 2019	DNES-003	Gate 3	NNS 2020	157190	2020
September 2019	DNES-003	Gate 2	NNS 2020	76917	2020
October 2019	DNES-003	Gate 3	NNS 2020	102098	2020

[v-xicai · Accepted solution]

Hi @ShiMaria ,

You may create calculated column and measure like DAX below .

Column:

Gate_Number= RIGHT(Table1[Gate], 1)



Measure:

SUMmed Total =
VAR _MaxGate =
    CALCULATE ( MAX ( Table1[Gate_Number] ), ALLEXCEPT ( Table1, Table1[NNS Year Pure], Table1[DNES] ) )
RETURN
    CALCULATE (
        SUM ( Table1[NNS Value] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[NNS Year Pure], Table1[DNES] ),
            Table1[Gate_Number] = _MaxGate
        )
    )

Best Regards,

Amy 

Community Support Team _ Amy

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

[amitchandak]

@ShiMaria , assuming DNES is the project name, else replace column.

Logic if the count of gate = count of gate 1, do not include

//per project , per year
sumx(filter(summarize(Table, Table[DNES], Table[NNS Year Pure], "_1", sum(Table[NNS Value]), "_2", count(Table[Gate]) , "_3", calculate(count(Table[Gate]),Table[Gate] ="Date 1"))
, [_2] <> [_3]),[_1])

//per project
sumx(filter(summarize(Table, Table[DNES], "_1", sum(Table[NNS Value]), "_2", count(Table[Gate]) , "_3", calculate(count(Table[Gate]),Table[Gate] ="Date 1"))
, [_2] <> [_3]),[_1])

[ShiMaria]

Hello @amitchandak , 

DNES column contains the project name/number. 

when I apply the formula you proposed, it sum all the gates values. 

though, I would like that it sum only values from the latest gate (per project, per year). 

thank you in advance for your help, 

Maria