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

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

6 REPLIES 6
OwenAuger
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!

My Blog
Connect on Twitter
Connect on LinkedIn
HalilG
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. 

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!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

HalilG
Frequent Visitor

I understand what u meant @OwenAuger, thank you.

HalilG
Frequent Visitor

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

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 Kudoed Authors