Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

How to sum a filtered field

Hi,

 

Im having the following problem. 

 

In the graphs you can see the values by all the products and in the table below its values by product:

 

The values by product (table) are perfect, exactly what I want, because its "filtering" by product.

 

The problem is with the Spend Non - Recurring Product that basicaly is a product that was bought the past year but not this year. As you can see in the picture the "Spend Selection Y-1" contains values but the "Spend Selection Y" doesnt so that means that we bought it last year but not this year.

 

The measure is:

Volume Impact (non recurring) = IF([Net Net Amount CY] < 0,[Spend Delta Absolute],0)
 
The [Spend Delta Absolute] = [Net Net Amount CY] - [Net Net Amount PY] 
 

So as you can imagine this works perfect for a unique product because you can check if that product is [Net Net Amount CY] < 0  and then take the value but when you dont filter by a product the [Net Net Amount CY]  will never be 0 again because its summing all the amounts for all the products and because of this I have 0 in the graph, so the problem I think is in the "IF" condition but I cant imagine making it in another way, someone can help me with this?

 

thanks,

 

 

 

Need Help

3 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous  This is a challenge for DAX context. You need to put the product row filter into your DAX measure. 

     

    Try something like this: 

     

    Volume Impact (non recurring) = 

    SUMX(VALUES(Product[Name]), IF([Net Net Amount CY] < 0,[Spend Delta Absolute],0) )

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi, many thanks for your help.

     

    I have adapted your measure and it looks like:

    SUMX(VALUES(DIM_INVOICE_PRODUCT[HARMONIZED_PRODUCT_ID]), IF([Spend Delta Absolute] < 0,[Spend Delta Absolute],0))
     
    but is not giving me the number I want:

     

     

    At least now we have some values on the bar but still needing to adjust it

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous,

      Did any particular filter that you applied in the visuals? AFAIK, the filter added in expression or visual level filter only works with the formula and visual itself.
      If you want to invoke the calculation results in other visuals, you need to manually set groups(e.g. summarize function) and reproduce corresponding filters on these contexts for calculating.

      All the secrets of SUMMARIZE - SQLBI

      Regards,
      Xiaoxin Sheng