Getting the total value in all the rows

Question

I'm using the calculation below to calculate the sum of the amount for accounts >= 200 And the problem I have is when I visualize Account with Account total with excel, it gives me the total amount in all accounts. How can I solve this?`

Account total:= CALCULATE(SUM('Table'[amount]),'Table'[Type]= "ABC",'Table'[account] >=200)

Answer 1

You should be using:

CALCULATE (
    SUM ( 'Table'[amount] ),
    FILTER ( 'Table', 'Table'[Type] = "ABC" && 'Table'[account] >= 200 )
)

The difference is that your current formula is equivalent to:

CALCULATE (
    SUM ( 'Table'[amount] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Type] = "ABC" && 'Table'[account] >= 200 )
)

i.e. identical to that which I give apart from the crucial difference that it applies an (in your case implicit) ALL to the table prior to filtering. This implicit ALL will override any filters you may be applying externally.

Answer 2

@Jos is mostly correct but there are some small inaccuracies.

This code

CALCULATE (
    SUM ( 'Table'[amount] ),
    'Table'[Type] = "ABC",
    'Table'[account] >= 200
)

is equivalent to

CALCULATE (
    SUM ( 'Table'[amount] ),
    FILTER ( ALL ( 'Table'[Type] ), 'Table'[Type] = "ABC" ),
    FILTER ( ALL ( 'Table'[account] ), 'Table'[account] >= 200 )
)

not

CALCULATE (
    SUM ( 'Table'[amount] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Type] = "ABC" && 'Table'[account] >= 200 )
)

In particular, if you had a filter on, say, 'Table'[Category], this would be preserved in the former but not in the latter since ALL ( 'Table' ) removes filters on all of the columns, not just [Type] and [account].

---

I propose the following two nearly equivalent solutions, which are slightly more computationally efficient than filtering an entire table:

CALCULATE (
    SUM ( 'Table'[amount] ),
    FILTER ( VALUES ( 'Table'[Type] ), 'Table'[Type] = "ABC" ),
    FILTER ( VALUES ( 'Table'[account] ), 'Table'[account] >= 200 )
)

or

CALCULATE (
    SUM ( 'Table'[amount] ),
    KEEPFILTERS ( 'Table'[Type] = "ABC" ),
    KEEPFILTERS ( 'Table'[account] >= 200 )
)

---

More on KEEPFILTERS: https://www.sqlbi.com/articles/using-keepfilters-in-dax/