sum only some row in a column

[Anonymous]

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!

[v-juanli-msft · Accepted solution]

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.

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

[Anonymous]

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!

[Anonymous]

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