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.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors