cancel
Showing results for
Did you mean:
Frequent Visitor

## 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.

I wrote the below code and it didnt work. I have tried SUMX (Values(Date[Year]) variation, it didnt show anything.

Any idea to bring total modified value to year row?

```Budget Modified =
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] )
)
)```
1 ACCEPTED SOLUTION
Super User I
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?

Owen Auger

My Blog
6 REPLIES 6
Super User I

Hi @HalilG

Hmm...a SUMX pattern should work as long as you sum over months.

I would recommend that you:

1. Rename your Budget Modified measure to Budget Modified Monthly
2. 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

Owen Auger

My Blog
Frequent Visitor

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.

Super User I
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?

Owen Auger

My Blog
Frequent Visitor

I understand what u meant @OwenAuger, thank you.

Frequent Visitor

Could u pls share your code Owen ? I'll check that and let u know. I appreciate your help.

Super User III

Hi,

Try this

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements