Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

sum only some row in a column

Hi,

i need to sum a value column by an if condition like this

 

That's my DB example

 

I need to sum "Valore" column only if "ITEMID" & "VENDACCOUNT" (my key) have blank "FP Fornitore" only while there is at least 1 row that isn't blank.

Expected result: € 3630

 

So, if my "ITEMID" & "VENDACCOUNT" have an "FP Fornitore" i need the sum the rest

If my "ITEMID" & "VENDACCOUNT" don't have at least one row in "FP Fornitore" i will not sum

Another example:

Excepted result: 0

DB doesn't have any row with "FP Fornitore" by "ITEMID" & "VENDACCOUNT"

Last example:

Expected result: €1936

Hope that's a good explanation

Thanks!

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Calculate measures

    Measure 2 =
VAR disc =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[VENDACCOUNT] ),
        ALLEXCEPT (
            'Table',
            'Table'[ITEMID]
        )
    )
VAR con =
    CONCATENATEX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[ITEMID]
                = MAX ( 'Table'[ITEMID] )
        ),
        [FP fornitore]
    )
RETURN
    IF (
        disc > 1
            && con
                <> BLANK (),
        1
    )

Measure =
CALCULATE (
    SUM ( 'Table'[Valore] ),
    FILTER (
        ALLEXCEPT (
            'Table',
            'Table'[ITEMID]
        ),
        'Table'[FP fornitore]
            = BLANK ()
            && [Measure 2] = 1
    )
)

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I need to see if we bought the same ITEM from different vendor, so i need to sum the value for each 

       

      Cause we can buy item A from vendor X and item A from vendor Y, maybe Y vendor has a better contract ( column "FP Fornitore"), so i need to sum the value that we lost with the other vendor X

       

      While, if we buy item A only from one seller there is no problem!

       

      Hope that's better

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago



      That's good i think

      We bought item AX338 from F*096 and F*061 but we have the contract only with F*061, so the rest are "lost money" 

      I need to sum the loss, yellow cells!

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

    Hi Anonymous 

    Calculate measures

    Measure 2 =
VAR disc =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[VENDACCOUNT] ),
        ALLEXCEPT (
            'Table',
            'Table'[ITEMID]
        )
    )
VAR con =
    CONCATENATEX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[ITEMID]
                = MAX ( 'Table'[ITEMID] )
        ),
        [FP fornitore]
    )
RETURN
    IF (
        disc > 1
            && con
                <> BLANK (),
        1
    )

Measure =
CALCULATE (
    SUM ( 'Table'[Valore] ),
    FILTER (
        ALLEXCEPT (
            'Table',
            'Table'[ITEMID]
        ),
        'Table'[FP fornitore]
            = BLANK ()
            && [Measure 2] = 1
    )
)

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.