Modifying Current Month and Showing Grand Total

[HalilG]

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] )
    )
)

[OwenAuger]

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

[HalilG]

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. 

[OwenAuger · Accepted solution]

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?

[HalilG]

I understand what u meant @OwenAuger, thank you.

[HalilG]

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

[Ashish_Mathur]

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.