Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sebastian
Advocate II
Advocate II

baddest Month of each Year based on value

Hi all,

 

I have a table wich consists of following columns: date, value, area, customer

 

I try to find a way to show the baddest month of each year based on the sum of value.

 

Did someone has an solution how to realize this?

 

 

Thanks.

2 ACCEPTED SOLUTIONS
KHorseman
Community Champion
Community Champion

@Sean I've cracked it! This might be my favorite formula I've ever written. And I couldn't have figured it out without DAX Studio because nesting two contradictory TOPNs is impossible to keep straight in your mind.

 

OK, so here's the setup: same basic data as this whole thread has been about, plus one extra table called RankTable. That table just has a column named [Rank] which is a continuous series of integers, 1-whatever. 1-10 in my example but you could go as high as you need. That table has no relationships to anything. The desired output is that you drop that column as rows in a table visual or something like that, and you place a measure next to it called [Top Months]. On each row, you get the row-th ranked month by sales. So instead of just giving you the name of the month with the worst sales like we started out with, or the best sales, row 8 gives you the 8th best. You can filter RankTable[Rank] so that it only displays the top 5, or only the 7th, or whatever, and it will always return based on whatever number or numbers are showing in that column.

 

This requires one supplemental measure.

RowTest = FIRSTNONBLANK(RankTable[Rank], 1)

Here's the formula*:

 

Top Months = FIRSTNONBLANK(
	SUMMARIZE(
		TOPN(
			1,
			TOPN(
				[RowTest],
				SUMMARIZE(
					SalesTable,
					DateTable[Month of Year],
					"Monthly Sales",
					SUM(SalesTable[value])
				),
				[Monthly Sales],
				0
			),
			[Monthly Sales],
			1
		),
		[Month of Year]
	),
	1
)

Basically it first does a TOPN of the summarized table based on the number fed to it by the RankTable[Rank] row context. So on row 5 it gives you the top 5 highest sales. Then it does a second TOPN going in the reverse direction, giving you the lowest ranked value of that first TOPN set. For row 1 it only has the 1 highest anyway, but for any row below 1 you want to discard everthing higher than that row. Make sense? Probably not, it's hard to picture. Here, have a test file.

 

 If you want the bottom n results (same thing but counting up from the worst) you would just reverse the 1 and 0 from the two nested TOPNs. So...

 

Bottom Months = FIRSTNONBLANK(
	SUMMARIZE(
		TOPN(
			1,
			TOPN(
				[RowTest],
				SUMMARIZE(
					SalesTable,
					DateTable[Month of Year],
					"Monthly Sales",
					SUM(SalesTable[value])
				),
				[Monthly Sales],
				1
			),
			[Monthly Sales],
			0
		),
		[Month of Year]
	),
	1
)

The example file also has two bonus measures for returning the corresponding sales amounts that produced these top and bottom rankings. Enjoy!

 

 

*Well, almost the formula. I did an extra thing for tie breaking in the real formula but I just wanted to show the basics to make the thing work first. In the example file you'll see that the second outer TOPN is not based on [Monthly Sales]; it's actually on [Monthly Sales] + (1 / [Month Index]). Month Index is an extra column in my date table that is just a unique integer index number. I think this means that in a tie the earlier of the tied months will rank lower, but this formula reverses sort orders so many times that I'm still having trouble keeping it straight.

 

@Sebastiansorry again for the continental topic drift. I do hope we managed to answer your actual question among all these other posts...





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

Proud to be a Super User!




View solution in original post

I lied. Obviously you can do it in DAX. I wrote those query formulas almost a year ago when I didn't know nearly as much, and never thought about them again. Of course I could come up with a better way now if I thought about it for a minute.

 

First, add a column that should really be there anyway. Formatted the same way as WeekNumber, but for Month of Year:

 

Month Val = CONCATENATE(YEAR(DateTable[Date]), CONCATENATE(IF(MONTH(DateTable[Date]) < 10, "0", ""), MONTH(DateTable[Date])))

 

Technically you could do this without that column, but I want it to avoid circular dependencies when picking another column as a sort order for Month of Year. Anyway...

 

MonthIndex = CALCULATE(DISTINCTCOUNT(DateTable[Month Val]) + 1, FILTER(ALL(DateTable), DateTable[Month Val] < EARLIER(DateTable[Month Val])))

 

You could substitute Month of Year there instead of Month Val, but then you have to use Month Val as the sort order for Month of Year.





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

Proud to be a Super User!




View solution in original post

33 REPLIES 33

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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