Forum Discussion

robg89's avatar
robg89
Regular Visitor
4 years ago
Solved

Urgent help needed for SUM function

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? 🙂

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    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

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi robg89 

    Can you please give the below measure a try

    CALCULATE(SUM(Table1[value]),NOT(ISBLANK(TABLE1[Product])))
    • robg89's avatar
      robg89
      Regular Visitor
      4 years ago

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

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    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