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, Dec 10 '20 at 23:30
Updated. Unfortunately I am using excel 2016 and I don’t have REMOVEFILTERS function. — Wadstk, Dec 10 '20 at 23:42

sergiom · Accepted Answer · 2020-12-11T08:47:09.153

1

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

edited Dec 11 '20 at 08:47

answered Dec 10 '20 at 23:29

sergiom

4,791
3
24
32

Unfortunately I am using excel 2016 and I don’t have the REMOVEFILTERS function. – Wadstk Dec 10 '20 at 23:42
1

No big deal, REMOVEFILTERS is just syntactic sugar for ALL – sergiom Dec 10 '20 at 23:45
This gets me the right number, but returns every Account regardless of whether it owns the Bond or not. See the short edit in the question. Any way I can return only those accounts that own Bond #1 (Amt is not blank)? – Wadstk Dec 11 '20 at 00:08
1

I edited the answer adding a test on the existence of a row with the current combination of Bond and Account – sergiom Dec 11 '20 at 08:49
Wonderful! Exactly what I needed. – Wadstk Dec 11 '20 at 16:02

Measure Total in DAX

1 Answers1