CODEWITHSUNDEEP
daxpowerbi-desktopssas-tabularssas-2012
hana
hana

Reputation: 121

Getting the total value in all the rows

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)

Upvotes: 0

Views: 570

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40244

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

Upvotes: 1

Jos Woolley
Jos Woolley

Reputation: 9062

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.

Upvotes: 0

Related Questions