Skip to main content
cancel
Showing results for 
Search instead 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

Reply
dimitrishuk
Helper I
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!!

 

Capture.PNG

 

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
dimitrishuk
Helper I
Helper I

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. 

 

q2.PNG

 

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.

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?

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.

Hi Qiuyun , thanks for your reply.

 

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?

v-qiuyu-msft
Community Support
Community Support

Hi @dimitrishuk,

 

I still can't access the link. You can upload it to your Dropbox and share the link to everyone and paste the link here. 

 

q5.PNG

 

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.
dimitrishuk
Helper I
Helper I

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

v-qiuyu-msft
Community Support
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.
v-qiuyu-msft
Community Support
Community Support

Hi @dimitrishuk,

 

I'm not able to access and download your pbix file. Please share it again. 

 

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.

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.