cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## DAX measure using variables is displaying column total as 0

Hi I'm facing a strange issue with a measure that is not SUMMING totals on the bottom powerpivot row or in power bi and cant figure out why. Is this a bug or some predictable behaviour that I'm missing?

The result should be 191,756.00 not 0 for the 1000+ days column. The strange thing is that a similar formula for 1-30 days works fine!!

Find a link to the sample pbix here

Formulas written out:

1000+ Days:=

VAR
MaxDate = MAX('Allocation Date Dimension'[Date])

VAR BalanceBucketDebits =
CALCULATE(
SUM('Accounts Receivable Ledger'[Amount - Gross]),
'Accounts Receivable Ledger'[Amount - Gross]>0,
'Accounts Receivable Ledger'[Invoice Due Date]<=(MaxDate-1000),
'Accounts Receivable Ledger'[Allocation Date]<=MaxDate
)

VAR BalanceBucketCredits =
CALCULATE(
SUM('Accounts Receivable Ledger'[Amount - Gross]),
'Accounts Receivable Ledger'[Amount - Gross]<0,
'Accounts Receivable Ledger'[Draft- Due Date]<=(MaxDate-1),
'Accounts Receivable Ledger'[Allocation Date]<=MaxDate
)

VAR BucketBalance=

BalanceBucketDebits+BalanceBucketCredits
RETURN

ROUND(IF(BucketBalance<0,0,BucketBalance),2)

Strange thing is, if I add row hierarchies sums work fine, and are correct but the total of all rows displays 0. The result should not be 0. Strangely, the following works fine!

1-30 Days:=

VAR MaxDate =

MAX('Allocation Date Dimension'[Date])

VAR CurrentBucketDebits =

CALCULATE(

SUM('Accounts Receivable Ledger'[Amount - Gross]),

'Accounts Receivable Ledger'[Amount - Gross]>0,

'Accounts Receivable Ledger'[Invoice Due Date]>=(MaxDate-30),

'Accounts Receivable Ledger'[Invoice Due Date]<=(MaxDate-1),

'Accounts Receivable Ledger'[Allocation Date]<=MaxDate )

VAR LastBucketDebitsBalance =

CALCULATE(

SUM('Accounts Receivable Ledger'[Amount - Gross]),

'Accounts Receivable Ledger'[Amount - Gross]>0,

'AccountsReceivable Ledger'[Invoice Due Date]<=(MaxDate-31),

'Accounts Receivable Ledger'[Allocation Date]<=MaxDate )

VAR Credits =

CALCULATE(

SUM('Accounts Receivable Ledger'[Amount - Gross]),

'Accounts Receivable Ledger'[Amount - Gross]<0,

'Accounts Receivable Ledger'[Draft- Due Date]<=(MaxDate-1),

'Accounts Receivable Ledger'[Allocation Date]<=MaxDate )

VAR CreditBalanceBF=

IF(LastBucketDebitsBalance+Credits<0,LastBucketDebitsBalance+Credits,0)

RETURN

ROUND(IF( CurrentBucketDebits+CreditBalanceBF<0, 0, CurrentBucketDebits+CreditBalanceBF ),2)

Whats the difference??

9 REPLIES 9
Helper I
Community Support

Hi @dimitrishuk,

After looking into your pbix file, I find the BalanceBucketDebits + BalanceBucketCredits value is less than 0, so based on the condition IF( CurrentBucketDebits+CreditBalanceBF<0, 0, CurrentBucketDebits+CreditBalanceBF ), the final value for the measure 1000+ Days returns 0. So what you got is expected.

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

Ok, so perhaps I need to change my measure a little. This is what I want to achieve.

Each row for address number returns a result.

0 if CurrentBucketDebits+CreditBalanceBF < 0, else returns the sum of CurrentBucketDebits+CreditBalanceBF. As you correctly point out that condition correctly returns 0.

The outcome I am looking for is a sum of those positive balances. So my (mistaken) assumption was that the total is a calculation of the sum of rows, rather than an independant evaluation of the total.

How would you change the formula to achieve this?

Community Support

Hi @dimitrishuk,

Please clarify about this "The outcome I am looking for is a sum of those positive balances. So my (mistaken) assumption was that the total is a calculation of the sum of rows, rather than an independant evaluation of the total. ".

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

The result I am looking for in the Grand Total for the column is the sum of the values of the rows - after if conditions have been applied.

Not recalculated without the context filters ie the address number

Does this make sense?

Community Support

Hi @dimitrishuk,

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

Hi @v-qiuyu-msft I have shared the file again. The link should work now.

Community Support

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support

Hi @dimitrishuk,

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.