topic Re: SUM Filtered in DAX Commands and Tips

SUM Filtered

Anonymous — Thu, 30 Jul 2020 15:56:06 GMT

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?

Re: SUM Filtered

Greg_Deckler — Thu, 30 Jul 2020 15:58:25 GMT

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

Re: SUM Filtered

Anonymous — Thu, 30 Jul 2020 16:08:49 GMT

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

Re: SUM Filtered

Greg_Deckler — Thu, 30 Jul 2020 16:20:18 GMT

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