Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

SUM Filtered

Hey, I'm stuck on something I think should be simple but im going a bit mad getting there. I want to sum a total of everything in a table, where column A = the value of the current row. So if column A contains Apples, and column B contains costs of Applies, i want a column that shows the total costs of all rows of Apples

 

Fruit       Cost       Subtotal

Apple       1              4

Apple       3              4

Banana     2              2

Orange    4               4

 

However, i want it to show the full subtotal, regardless of if the table is filtered. The table is being filtered by other measures on the same table I've tried ALL/ALLEXCEPT, and it seems to repeat every row on the table, even when filtered by Apple. What am i missing please?

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Anonymous - So generally you are going to use SUMX(FILTER('Table'), [Column] = "Something"),[Value]) or CALCULATE(SUM('Table'[Value]),FILTER('Table',[Column] = "Something"))

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks Greg. The problem I have is getting it to ignore existing filters. I tried this, but it seems to just think forever as soon as i add the all()

       

      POAccrualValue =
      VAR REMARK = SELECTEDVALUE('Account Items (F0911)'[Name - Remark Explanation])
      RETURN
      CALCULATE('Account Items (F0911)'[AccountItemValue],FILTER(all('Account Items (F0911)'),'Account Items (F0911)'[Name - Remark Explanation]=REMARK))
      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        Anonymous - I can't make heads or tails of that formula because it doesn't seem to relate back to the sample data.

         

        With your sample data as reference, if you have "Fruit" in a visual, you could do this:

         

        Measure =
  VAR __Fruit = MAX('Table'[Fruit])
RETURN
  SUMX(FILTER(ALL('Table'),'Table'[Fruit] = __Fruit),[Cost])

or

Measure =
  VAR __Fruit = MAX('Table'[Fruit])
RETURN
  CALCULATE(SUM('Table'[Cost]),FILTER(ALL('Table'),'Table'[Fruit]=__Fruit))