Forum Discussion
Solved
Modifying Current Month and Showing Grand Total
Hi, I have modified summation of budget values for the current month. Everyting is fine, however I would like to show total modified budget on year row like normal budget summation. ...
- Ok...
I've certainly used the SUMX ( VALUES ( 'Date'[Month] ), ... ) pattern successfully in my own scenarios.
Just one thing to check:
When the 2nd argument of SUMX is an expression that is correct per month, it must be wrapped in CALCULATE (or be a measure with an implied CALCULATE as in my example).
Could you post back a shareable pbix if it's still not working?
Hi HalilG
Hmm...a SUMX pattern should work as long as you sum over months.
I would recommend that you:
- Rename your Budget Modified measure to Budget Modified Monthly
- Create a new Budget Modified that sums Budget Modified Monthly for each month
Budget Modified should then display the correct total for any aggregation of month, e.g. at year level.
Budget Modified Monthly =
IF (
HASONEVALUE ( 'Date'[Month] );
IF (
VALUES ( 'Date'[Month #] ) = MONTH ( TODAY () )
&& VALUES ( 'Date'[Year] ) = YEAR ( TODAY () );
DIVIDE ( SUM ( 'Budget'[BudgetAmount] ); 30 ) * DAY ( TODAY () );
SUM ( 'Budget'[BudgetAmount] )
)
)
Budget Modified =
SUMX (
VALUES ( 'Date'[Month] ),
[Budget Modified Monthly]
)Regards,
Owen
Thanks for replying Owen but I am trying to put modified total in the year row, as it is like normal summation.
I already tried variation of SUMX pattern for year and then month in the same formula but it does not work.
- Ok...
I've certainly used the SUMX ( VALUES ( 'Date'[Month] ), ... ) pattern successfully in my own scenarios.
Just one thing to check:
When the 2nd argument of SUMX is an expression that is correct per month, it must be wrapped in CALCULATE (or be a measure with an implied CALCULATE as in my example).
Could you post back a shareable pbix if it's still not working?Could u pls share your code Owen ? I'll check that and let u know. I appreciate your help.
Hi,
Try this
=IF(HASONEVALUE(Calendar[Month]),[your measure for computing budget modified],SUMX(SUMMARIZE(VALUES(Calendar[Month]),[Month],"ABCD",[your measure for computing budget modified]),[ABCD]))
Hope this helps.
