SUM Filtered

[Anonymous]

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?

[Greg_Deckler]

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

[Anonymous]

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]

@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))