Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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??
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
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
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?
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.
Best Regards,
Qiuyun Yu
Hi @dimitrishuk,
I'm not able to access and download your pbix file. Please share it again.
Best Regards,
Qiuyun Yu
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
89 | |
86 | |
82 | |
64 | |
49 |
User | Count |
---|---|
124 | |
110 | |
88 | |
68 | |
66 |