daxpowerbi-desktopssas-tabularssas-2012

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)

Solution

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