cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Jarodp42 Frequent Visitor
Frequent Visitor

Sum values by dynamic month

Hi everyone, I am new to power bi and trying to build a measure that will allow me to sum the total value by month dynamically and return the max value for that person. Can anyone help, please! 

 

I am currently using this formula to get the sum by month, but I am not sure how to get the max value based on the sum by month.

 

=

CALCULATE(
SUM('Vendor Project'[Values]),
ALLEXCEPT('Vendor Project','Vendor Project'[YearMonth],'Vendor Project'[Names]

 

Here is a sample of data

NamesValuesMonthYear
James150Jan-19
James200Jan-19
James200May-19
James125Jun-19
James175Jun-19
Gaby75May-19
Gaby300May-19
Gaby350Sep-19
Gaby250Sep-19
Gaby225Sep-19
Gaby125Apr-19
Gaby150Apr-19
Gaby275Apr-19
Gaby350Feb-19

The results I am looking for are:

James - 350

Gaby - 825

 

Also, Is there a way to put this into a gauge visual as the maximum?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

Re: Sum values by dynamic month

Hi @Jarodp42 ,

You can use this measure and put it into a table visual together with the names: (I called my table Names btw):

Measure = 
VAR _groupedTable = SUMMARIZE(Names, Names[MonthYear], "SUM", SUM(Names[Values]))
RETURN
MAXX(_groupedTable, [SUM])

Result:

image.png

You can put this measure into the Gauge target lable, but remember that it will be evaluated for the current data context. So, with your sample data, it will evaluate to 850 but not because Gaby has the highest, but because the month Sept-19 is overall the highest. 
let me know if this answers your question 🙂

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂


Did I answer your question? Mark my post as a solution!
Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
Super User I
Super User I

Re: Sum values by dynamic month

Hi @Jarodp42 ,

You can use this measure and put it into a table visual together with the names: (I called my table Names btw):

Measure = 
VAR _groupedTable = SUMMARIZE(Names, Names[MonthYear], "SUM", SUM(Names[Values]))
RETURN
MAXX(_groupedTable, [SUM])

Result:

image.png

You can put this measure into the Gauge target lable, but remember that it will be evaluated for the current data context. So, with your sample data, it will evaluate to 850 but not because Gaby has the highest, but because the month Sept-19 is overall the highest. 
let me know if this answers your question 🙂

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂


Did I answer your question? Mark my post as a solution!
Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

View solution in original post

HamiltonVT Member
Member

Re: Sum values by dynamic month

Create a Max column like the one you have. (Mine was this)

 

Measure =
CALCULATE(
SUM('Table'[Values]),
ALLEXCEPT('Table','Table'[MonthYear],'Table'[Names]))
 
If you choose Max of Measure in the Values Fields section as well as the Names column you will be given the values you want on the the visualizaitons

 

 

 

Jarodp42 Frequent Visitor
Frequent Visitor

Re: Sum values by dynamic month

@JarroVGIT Thank you for your response. I was having to build it in two steps but your measure allowed me to get my desired outcome in one measure! As for the Guage visual that makes sense as it wouldn't know which name to filter for. Please correct me if I am wrong, but I could throw a page filter on for names and when selecting certain names it should give me their max value by month, right? Thanks again for helping with my issue!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors