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