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" onl...
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.

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