Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Antmkjr
Post Patron
Post Patron

Total not working correctly

https://drive.google.com/file/d/1SztSty7XnnX9Z4TBsVS25nDWON381Fuo/view?usp=sharing

 

In the above file,

1) why is total not displayed for funding?

The formula for funding is :

Funding =
IF([Balance Current Month]<0,[Balance Current Month]*-1)
The expected Total is 139.7M

AnuTomy_1-1600691767488.png

 

2) 

 

AnuTomy_2-1600691828639.png

I want the total opening balance to be of the current month, and also the opening balance of 2020 should be of the current month,ie. 16M

 

Formula:

IF (
CurrentMonth = SELECTEDVALUE ( 'Date'[MonthOfYear] )
&& CurrentYear = VALUE ( SELECTEDVALUE ( 'Date'[Year] ) ),[TB Amount Total],[Cumulative Balance Updated]-[Balance Current Month Updated])
RETURN
Result

 

What changes in the formula is required?

 

3 REPLIES 3
dedelman_clng
Community Champion
Community Champion

Hi @Antmkjr  - 

 

****WARNING - DISCLAIMER FOR THIS SOLUTION*****

I can give you formulas for the specific things that you are asking for, but I'm not sure how helpful they will be in the long run, as they will likely not work at the month level. If you can describe in a narrative, what it is the report is doing, we might be able to find a better way to do it.

****WARNING - DISCLAIMER FOR THIS SOLUTION*****

 

One thing to know is that Total in PowerBI is nothing like Total in an Excel pivot table. Total evaluates the measure in the context of the subtotal or grand total where it sits (in this case, over all years).  So when your code for Funding says:

 

 

Funding = IF([Balance Current Month]<0,[Balance Current Month]*-1)

 

 

[Balance Current Month] is evaluated at the "Total" context, and therefore so is Funding. It is not a SUM of the consituent rows/columns - it is re-evaluated.

 

We can get around that with HASONEVALUE

 

 

Funding =
IF (
    HASONEVALUE ( 'Date'[Year] ),   //This is calculating at the year level
    IF ( [Balance Current Month] < 0, [Balance Current Month] * -1 ) + 0,
    CALCULATE (                     //This is calculating at the "Total" level
        SUMX (
            FILTER ( VALUES ( 'Date'[Year] ), [Balance Current Month] < 0 ),
            [Balance Current Month] * -1
        )
    )
)

 

 

Somewhat similarly with Opening Balance - your code has a month and a year, but in this visual you only have a year.  So since it is September, TODAY() gives 9 for the month.  But MONTH(SELECTEDVALUE(Date[Year]) in 12, so you're not getting a match.

 

This code removes the check on month and puts 16M where you want it to be.

 

 

Opening Balance = 
VAR TimeTravel =
    CALCULATE ( MAX ( 'Date'[Date] ), PARALLELPERIOD( 'Date'[Date], -1, MONTH ) )
VAR CurrentDate =
    TODAY ()
VAR CurrentMonth =
    MONTH ( CurrentDate )
VAR CurrentYear =
    YEAR ( CurrentDate )
VAR Result =
 IF (
             CurrentYear = VALUE ( SELECTEDVALUE ( 'Date'[Year] ) ),
             [TB Amount Total],[Cumulative Balance Updated]-[Balance Current Month Updated])
RETURN
    Result

 

 

2020-09-21 14_52_58-check (3) (1) - Power BI Desktop.png

Again, please note the disclaimer at the top.

 

Hope this helps

David

@dedelman_clng 

What I am trying to do is calculating Forecasted Payments, Forecasted Collections, Forecasted Opening, Funding and Forecasted Closing Balances.

Current Balance is TB Balance = Opening Balance of Current Month

Current Month Closing Balance = Opening Balance of Current Month +forecasted Collection -Forecasted Payments 

If Current Month Closing Balance is negative , we add an equal amount of Funding

So Current Month Closing Balance updated = Opening Balance of Current Month +forecasted Collection -Forecasted Payments +Funding

And we Cumulate this vCurrent Month Closing Balance updated to get the actual Closing balance across months , which drill up into years

 

1)

AnuTomy_0-1600747056414.png

The formulas should work even when drilled down into the month level.

When drilled down into Sep 2020, the opening balance should be the TB Amount total( Current Trial Balance), which is coming correctly in my file.(Screenshot 1)

 

2)

AnuTomy_1-1600747129585.png

 

But When rolling up into the year then 2020 opening balance should be also of current month, but currently is showing 0M,

this should be 16M(which is current month opening balance = TB Amount Total), also the total opening balance should be 16M in the above screen(and even when drilled down into the month level, the total opening balnce should be of the current month = TB Amount Total =16 M)(Screenshot 2)

 

3)

AnuTomy_2-1600748790537.png

 

Also all the formulas should work when drilling into a single year(even the new cumulative balance updated, which I now realized that is not working- Screenshot 3)

 

 

 

  

 

 

@Antmkjr - please try the suggestions I made already and see if you can take it from there. If not, please share *updated* pbix for further assistance.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.