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

 Names Values MonthYear James 150 Jan-19 James 200 Jan-19 James 200 May-19 James 125 Jun-19 James 175 Jun-19 Gaby 75 May-19 Gaby 300 May-19 Gaby 350 Sep-19 Gaby 250 Sep-19 Gaby 225 Sep-19 Gaby 125 Apr-19 Gaby 150 Apr-19 Gaby 275 Apr-19 Gaby 350 Feb-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

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

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.

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! 🙂

Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

3 REPLIES 3
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:

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.

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! 🙂

Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

Resolver I

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

Highlighted
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!

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors