Urgent help needed for SUM function

[robg89]

I am trying to create a measure whereby I sum values from one table only if a record exists in another table. See below for example.

In this example, I want to sum values in the Sales table, according to Month but I only want to do it if the record in the Product column appears in the Products table (also ignoring blank product lines from Sales table).

Ideas please? 🙂

[v-luwang-msft · Accepted solution]

Hi @robg89 ,

If you want to create a new column:

SUM = 
    CALCULATE (
        SUM ( Sales[Value] ),
        FILTER (
            ALL ( Sales ),
            Sales[Month] = EARLIER ( Sales[Month] )
                && Sales[Prodect] <> BLANK ()
                &&  LOOKUPVALUE ( Project[Product], Project[Product], Sales[Prodect] ) <> BLANK ()
        )
    )

And if masure:

SUM1 = 
    CALCULATE (
        SUM ( Sales[Value] ),
        FILTER (
            ALL ( Sales ),
            Sales[Month] = MAX ( Sales[Month] )
                && Sales[Prodect] <> BLANK ()
                &&  LOOKUPVALUE ( Project[Product], Project[Product], Sales[Prodect] ) <> BLANK ()
        )
    )

Base table:

Output result:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[pranit828]

Hi @robg89 

Can you please give the below measure a try

CALCULATE(SUM(Table1[value]),NOT(ISBLANK(TABLE1[Product])))

[robg89]

It doesn't work I am afraid.. I assume you need to reference the Product table in the ISBLANK formula?

[robg89]