Measure Total in DAX

Question

I have a simple data model with two dimensions and a fact table. The dimensions are called BondDim and AccountDim. They slice the fact table. The BondDim and AccountDim join to the fact table in a one-to-many relationship. I would like to create the measure "BondAmt" as shown here in bold outline:

I have tried this measure, but it seems to cross-join Bond and Account, and blows up the pivot table with a lot of blank rows:

=calculate([Amt], ALL(AccountDim), BondDim[Bond] = BondDim[Bond] )

How can I calculate the BondAmt totals column and have it be correct in whatever Bond and Account filter context I have?

EDIT:

As suggested by @sergiom, using

BondAmt = CALCULATE(SUM(Fact[Amt]), ALL(AccountDim))

returns the following:

It returns all of the accounts regardless of whether the Account owns the Bond (Bond #1 shown in the picture). How can I return only those accounts that have Amt in the bond?

what is the PortDim that appears in your DAX code and isn't mentioned in the question description? — sergiom
Updated. Unfortunately I am using excel 2016 and I don’t have REMOVEFILTERS function. — Wadstk

sergiom sergiom · Accepted Answer · 2020-12-10T23:29:08

It should be enough to remove the filter over AccountDim, since the existing filter over BondDim is not to be changed, like

BondAmt = CALCULATE(SUM(Fact[Amt]), ALL(AccountDim))

Edit: to avoid the rows with Accounts that don't own the bond we must add a test to check that the combination of Bond and Account is present in the fact table

BondAmt = 
IF (
    NOT ISEMPTY(Fact),
    CALCULATE (
        SUM ( Fact[Amt] ),
        ALL ( AccountDim )
    )
)

Measure Total in DAX

1 Answers