Forum Discussion
Total not working correctly
Community ChampionHi Anonymous -
****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
Again, please note the disclaimer at the top.
Hope this helps
David
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)
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)
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)
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)
- dedelman_clng
Anonymous - 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.