Forum Discussion

alexpork's avatar
alexpork
Frequent Visitor
5 years ago

How to filter without affecting running total

Hello! I have a column that has a variety of values. I would like to "filter" out the values that are less than 10,000,000 OR greater than -10,000,000. However, I do not want to affect the total at the bottom. I've noticed that with the filter pane, when I add this as a filter, it also affects the running total :

 

The current DAX code for this column is as follows:

 

Delta = SUM('Query' [2022] - SUM('Query'[2021])

 

Is there any straightforward way to edit this statement to filter based on the criteria above without affecting the total at the bottom?

 

 

For example, what I would like to show are these values in a table format. Instead of the 149,702,494 total, I would like to show the 198,450,703 in the picture above:

 

 

 

2 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    5 years ago

    Delta = VAR _total=SUM('Query' [2022] - SUM('Query'[2021]) RETURN IF(_total>10000000||_total<-10000000,_total)

    • alexpork's avatar
      alexpork
      Frequent Visitor
      5 years ago

      Hello! Thank you for the response.

      I tried running this statement but it is mentioning in the RETURN IF statement that the parameter is not in the correct type.