Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.

Reply
don_writer
Helper II
Helper II

DAX aggregate the highest value across three fields

‎08-08-2018 09:34 AM

Hi Power BI/DAX Gurus,

  • I have a table with MonthYear, OpenDate, RequisitionID, Group, and Widgets.
  • There could be repetition in a month of a RequisitionID (rare but it happens). If there is I'd only want to use the highest number of Widgets in a RequisitionID per Month.
  • Then I'd like to sum the Widgets per MonthYear per Group.
  • Then I'd like to have a second measure that sums the highest Widgets value per RequisitionID over the Quarter per Group.
  • Then sum the highest Widgets value per RequisitionID over the Year per Group.

Here's a sample datset:

data_as_ofMonthYearOpen_DateRequisitionIDGroupWidgets
1/31/2017Jan 20171/6/2017997BRDiv112
1/31/2017Jan 20171/18/20171015BRDiv116
1/31/2017Jan 201712/5/2016856BRDiv191
1/31/2017Jan 20171/6/2017938BRDiv61
1/31/2017Jan 201712/9/2016908BRDiv31
1/31/2017Jan 20171/9/2017990BRDiv291
1/31/2017Jan 20178/11/2016665BRDiv251
1/31/2017Jan 201712/12/2016927BRDiv91
1/31/2017Jan 20171/12/20171025BRDiv461
1/31/2017Jan 20171/18/20171043BRDiv11
1/31/2017Jan 201710/18/2016804BRDiv561
1/31/2017Jan 20171/26/20171061BRDiv461
1/31/2017Jan 20171/26/20171060BRDiv461
1/31/2017Jan 20177/29/2016639BRDiv481
1/31/2017Jan 20171/30/20171065BRDiv461
1/31/2017Jan 201712/1/2016862BRDiv271
1/31/2017Jan 201712/1/2016861BRDiv271
1/31/2017Jan 201712/1/2016863BRDiv271
1/31/2017Jan 20172/1/2016279BRDiv531
1/31/2017Jan 20179/2/2016714BRDiv561
1/31/2017Jan 20172/4/2016289BRDiv431
1/31/2017Jan 201712/5/2016917BRDiv11
1/31/2017Jan 20178/8/2016658BRDiv561
1/31/2017Jan 20178/9/2016657BRDiv611
1/31/2017Jan 20175/9/2016295BRDiv21
1/31/2017Jan 20178/10/2016662BRDiv611
1/31/2017Jan 20177/14/2016623BRDiv271
1/31/2017Jan 20177/14/2016610BRDiv271
1/31/2017Jan 20171/18/20171045BRDiv11
1/31/2017Jan 20177/19/2016632BRDiv521
1/31/2017Jan 201710/20/2016808BRDiv431
1/31/2017Jan 201710/20/2016810BRDiv431
1/31/2017Jan 20179/21/2016748BRDiv531
1/31/2017Jan 20171/23/20171051BRDiv291
1/31/2017Jan 20171/24/20171056BRDiv461
1/31/2017Jan 20172/25/2016338BRDiv511
1/31/2017Jan 20171/25/20171030BRDiv271
1/31/2017Jan 201712/29/2016973BRDiv691
1/31/2017Jan 201712/29/2016962BRDiv461
1/31/2017Jan 201711/29/2016882BRDiv431
1/31/2017Jan 20179/29/2016761BRDiv31
2/28/2017Feb 20171/6/2017997BRDiv112
2/28/2017Feb 20172/10/20171100BRDiv452
2/28/2017Feb 20172/6/20171079BRDiv551
2/28/2017Feb 20172/6/20171080BRDiv551
2/28/2017Feb 20171/6/2017938BRDiv61
2/28/2017Feb 20172/7/20171088BRDiv471
2/28/2017Feb 20172/7/20171084BRDiv481
2/28/2017Feb 201712/9/2016908BRDiv31
2/28/2017Feb 20171/9/2017990BRDiv291
2/28/2017Feb 20178/11/2016665BRDiv251
2/28/2017Feb 201712/12/2016927BRDiv91
2/28/2017Feb 20171/12/20171025BRDiv461
2/28/2017Feb 20171/18/20171043BRDiv12
2/28/2017Feb 201710/18/2016804BRDiv561
2/28/2017Feb 20172/21/20171108BRDiv11
2/28/2017Feb 20172/22/20171140BRDiv511
2/28/2017Feb 20172/22/20171139BRDiv511
2/28/2017Feb 20172/22/20171141BRDiv511
2/28/2017Feb 20171/26/20171061BRDiv461
2/28/2017Feb 20177/29/2016639BRDiv481
2/28/2017Feb 20171/30/20171065BRDiv461
2/28/2017Feb 20172/1/2016279BRDiv531
2/28/2017Feb 20179/2/2016714BRDiv561
2/28/2017Feb 20172/4/2016289BRDiv431
2/28/2017Feb 201712/5/2016917BRDiv11
2/28/2017Feb 20172/7/20171085BRDiv11
2/28/2017Feb 20178/8/2016658BRDiv561
2/28/2017Feb 20175/9/2016295BRDiv21
2/28/2017Feb 20172/13/20171073BRDiv91
2/28/2017Feb 20172/13/20171067BRDiv31
2/28/2017Feb 20171/18/20171045BRDiv11
2/28/2017Feb 20177/19/2016632BRDiv521
2/28/2017Feb 201710/20/2016808BRDiv431
2/28/2017Feb 201710/20/2016810BRDiv431
2/28/2017Feb 20179/21/2016748BRDiv531
2/28/2017Feb 20172/22/20171109BRDiv511
2/28/2017Feb 20172/23/20171128BRDiv291
2/28/2017Feb 20171/24/20171056BRDiv461
2/28/2017Feb 20172/25/2016338BRDiv511
2/28/2017Feb 201712/29/2016973BRDiv691
2/28/2017Feb 201711/29/2016882BRDiv431
2/28/2017Feb 20179/29/2016761BRDiv31
3/31/2017Mar 20172/10/20171100BRDiv452
3/31/2017Mar 20173/1/20171161BRDiv491
3/31/2017Mar 20172/6/20171079BRDiv551
3/31/2017Mar 20172/6/20171080BRDiv551
3/31/2017Mar 20171/6/2017938BRDiv61
3/31/2017Mar 20173/6/20171172BRDiv431
3/31/2017Mar 20172/7/20171084BRDiv481
3/31/2017Mar 201712/9/2016908BRDiv31
3/31/2017Mar 20178/11/2016665BRDiv251
3/31/2017Mar 20171/12/20171025BRDiv461
3/31/2017Mar 20173/14/20171192BRDiv461
3/31/2017Mar 20173/14/20171193BRDiv461
3/31/2017Mar 20171/18/20171043BRDiv11
3/31/2017Mar 201710/18/2016804BRDiv561
3/31/2017Mar 20172/21/20171108BRDiv11
3/31/2017Mar 20172/22/20171140BRDiv511
3/31/2017Mar 20172/22/20171139BRDiv511
3/31/2017Mar 20172/22/20171141BRDiv511
3/31/2017Mar 20171/30/20171065BRDiv461
3/31/2017Mar 20173/31/20171227BRDiv561
3/31/2017Mar 20172/1/2016279BRDiv531
3/31/2017Mar 20179/2/2016714BRDiv561
3/31/2017Mar 20172/4/2016289BRDiv431
3/31/2017Mar 20173/6/20171169BRDiv481
3/31/2017Mar 20173/8/20171091BRDiv11
3/31/2017Mar 20175/9/2016295BRDiv21
3/31/2017Mar 20172/13/20171073BRDiv91
3/31/2017Mar 20172/13/20171067BRDiv31
3/31/2017Mar 20173/13/20171175BRDiv61
3/31/2017Mar 20173/15/20171200BRDiv551
3/31/2017Mar 20173/17/20171204BRDiv431
3/31/2017Mar 20171/18/20171045BRDiv11
3/31/2017Mar 201710/20/2016808BRDiv431
3/31/2017Mar 20179/21/2016748BRDiv531
3/31/2017Mar 20172/22/20171109BRDiv511
3/31/2017Mar 20171/24/20171056BRDiv461
3/31/2017Mar 20172/25/2016338BRDiv511
3/31/2017Mar 201712/29/2016973BRDiv691
3/31/2017Mar 201711/29/2016882BRDiv431

 

The results should be:

MonthYearWidgets
Jan 201747
Feb 201745
Mar 201740
  
QuarterWidgets
174

 

While there isn't any duplicate RequisitionIDs in a month there are a lot of duplicates over the quarter (and year). I don't want to necessarily take the most recent nor the earliest. I'd want to pick up the highest number of Widgets in a RequisitionID.

 

So even though there is a RequisitionID of 1043BR for Jan, Feb, and Mar I only want to capture one of them. In this case Feb has 2 but Jan and Mar have 1 so for the Quarter I want to count only the 2.

 

Jan 20171/18/20171043BRDiv11
Feb 20171/18/20171043BRDiv12
Mar 20171/18/20171043BRDiv11

 

I know I am using MAX() in there somewhere but I'm not quite parsing that all correctly. Your expertise is very appreciated.

 

 

 

Best regards, 

~Don

Labels:
Message 1 of 3
413 Views
0
2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

‎08-10-2018 02:26 AM

@don_writer,

 

You may try using RANKX Function.

https://community.powerbi.com/t5/Desktop/Was-this-the-last-payroll-transaction-this-month/m-p/309301...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 2 of 3
404 Views
0
don_writer
Helper II
Helper II
In response to v-chuncz-msft

‎01-17-2019 01:57 PM

Finally getting back to this:

 

While I think my solution has something to do with RANKX this example doesn't help me get where I am going. This example wants to find the rank of a specific value for a column. I'm trying to sum the Widgets across an entire year but remove the duplicate [RequisitionID].

 

Thank for your input.

Message 3 of 3
374 Views
0

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

View All
Top Solution Authors
View All