cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

 

currentmonthtotal.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

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

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

6 REPLIES 6
Super User I
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

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

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?

Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

I understand what u meant @OwenAuger, thank you.

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors