Forum Discussion

ShiMaria's avatar
ShiMaria
Frequent Visitor
6 years ago
Solved

SUM based on several conditions

 

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 DNESGateNNS YearNNS ValueNNS Year Pure
March 2019DNES-001Gate 1NNS 201910182019
August 2019DNES-001Gate 3NNS 201910452019
July 2019DNES-001Gate 2NNS 201910452019
July 2017DNES-002Gate 1NNS 2019169652019
July 2019DNES-002Gate 3NNS 2019178442019
May 2019DNES-002Gate 2NNS 2019169652019
May 2019DNES-002Gate 2NNS 2019197582019
July 2019DNES-002Gate 3NNS 2019207822019
July 2017DNES-002Gate 1NNS 2019197582019
May 2019DNES-004Gate 1NNS 202017262020
May 2019DNES-004Gate 1NNS 202122222021
May 2019DNES-004Gate 1NNS 202232782022
July 2020DNES-004Gate 2NNS 202144092021
July 2020DNES-004Gate 2NNS 2022116312022
July 2020DNES-004Gate 2NNS 2023231942023
September 2019DNES-003Gate 2NNS 20201519682020
October 2019DNES-003Gate 3NNS 20202141732020
September 2019DNES-003Gate 2NNS 20203087762020
October 2019DNES-003Gate 3NNS 20203536872020
September 2019DNES-003Gate 2NNS 2020926052020
October 2019DNES-003Gate 3NNS 20201063052020
September 2019DNES-003Gate 2NNS 20201209672020
October 2019DNES-003Gate 3NNS 20201726942020
September 2019DNES-003Gate 2NNS 20203505722020
October 2019DNES-003Gate 3NNS 20205187732020
September 2019DNES-003Gate 2NNS 20202168312020
October 2019DNES-003Gate 3NNS 20203021312020
September 2019DNES-003Gate 2NNS 2020971802020
October 2019DNES-003Gate 3NNS 20201049582020
September 2019DNES-003Gate 2NNS 20201160002020
October 2019DNES-003Gate 3NNS 20201571902020
September 2019DNES-003Gate 2NNS 2020769172020
October 2019DNES-003Gate 3NNS 20201020982020
Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      ShiMaria
      Frequent Visitor
      6 years ago

      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

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    6 years ago

    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.