Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to Solution.
@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...
Proud to be a Super User!
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.
Proud to be a Super User!
Hi all and thanks a lot for the solution.
It is exactly the solution I'm looking for. Great.
I was still away and didn't notice that I got so many answers. Sorry for that.
Thanks a lot @ all.
Perhaps create a measure like:
Measure = SUM([Value])
Put the date and this measure in a column chart with date as axis. Drill down to month if necessary, the low bar would be your worst month.
Best I can do without more information.
Following this example...
You'll need a date table for this if you don't already have one. The date table should also have a column called Month, which is just the name of the month, as well as a Year column. Create a relationship between your table's date column and the date table's date. Then add the following measures (I've named the table you described "SalesTable" for convenience):
Sales = SUM(SalesTable[value])
Sales on Worst Month = MINX(VALUES(DateTable[Month]), [Sales])
Worst Month = IF(
ISBLANK([Sales on Worst Month]),
BLANK(),
CALCULATE(
FIRSTNONBLANK(DateTable[Month], 1),
FILTER(
VALUES(DateTable[Month]),
[Sales] = CALCULATE(
[Sales on Worst Month],
VALUES(DateTable[Month])
)
)
)
)
Then stick that Worst Month measure next to the DateTable[Year] and it will give you the name of the month with the worst sum of value.
Proud to be a Super User!
@KHorseman Nice solution!
I see you switched FIRSTDATE with FIRSTNONBLANK and so I made it work with my YYYY-MMM column and it really works great!
I'm about to modify it a bit and implement it for other data too.
I had never seen this post! Thanks again!
Slightly improved version of [Worst Month]. If you use my previous version on a table or matrix visual, it will show up in the Total row at the bottom as the lowest month of any year. That's meaningless, so now here's a version that does not show up in totals at all:
Worst Month = IF(HASONEVALUE(DateTable[Year],
IF(
ISBLANK([Sales on Worst Month]),
BLANK(),
CALCULATE(
FIRSTNONBLANK(DateTable[Month], 1),
FILTER(
VALUES(DateTable[Month of Year]),
[Sales] = CALCULATE(
[Sales on Worst Month],
VALUES(DateTable[Month of Year])
)
)
)
),
BLANK()
)
Proud to be a Super User!
@KHorseman The slighly improved version works great too!
However if anyone decided to use YYYY-MMM like me (instead of just month) - just use the first formula!
When you place in a table/matrix with the YEAR only
then the bottom total row will actually show you the all-time best/worst YYYY-MMM and amounts.
Here's what I mean...
@Sean yeah, actually when I was first figuring out this little puzzle I was using a MMM-YY column instead, because it was easier to see if my formula was really doing what I thought it was doing. I only switched to Month after I was sure it was giving the right answer for the right reason. You're absolutely right, the total only becomes nonsense when you're only using a month-only column.
The SUMMARIZE version should behave the same way; use a "Month of Year" column instead of Month and remove the IF(HASONEVALUE stuff to get the all-time worst in the totals row.
As an exercise to the reader, here's a quick puzzle: You can change Alt Worst Month to Alt Best Month by deleting two characters. Which ones?
Proud to be a Super User!
@KHorseman ",1" after [Monthly Sales] in the TOPN function? This appears to be the optional field to sort the totals by asc order. Removing that will sort the totals in the default descending order making the best month return first.
@Seth_C_Bauer bingo!
Proud to be a Super User!
@KHorseman And to save real estate we can combine the 2 like so...
Worst Year-Month & Amount = [Worst Year-Month] & " - " & FORMAT ( [Sales on Worst Year-Month], "Currency" )
@KHorseman Nice! I should just end the day on that high note instead of sticking my head back into my current DAX problem 🙂
@Seth_C_Bauer that just makes me wonder what that problem is.
@SeanI'm now convinced that there's some way to do a variant of one of these formula patterns that can be placed next to a column of numbers 1-10 that will return in order the top 10 of whatever. Some sort of combo with LASTNONBLANK and RANKX maybe but I haven't managed to make it work reliably yet.
This has been a productive thread. I sure hope it answered @Sebastian and we haven't just hijacked him without helping.
Proud to be a Super User!
@KHorseman Yes I'm sure @Sebastian is not expecting this!
BTW I still want to run your solution through DAX Studio ( and Wow I just noticed you've added 2 more paragraphs there )
@Sean yeah I decided to comment right after I finished my morning coffee. Dangerous...
Proud to be a Super User!
@KHorseman the SUMMARIZE solution works great too and it seems it is in fact faster! (5-7ms faster)
I ran both versions in DAX Studio because of the comments I saw on powerpivotpro saying the SUMMARIZE solution is faster
And it did actually run faster => 5-7ms faster (on a relatively small data set)
One final note => Again for people using YYYY-Month column => skip the IF ( HASONEVALUE ( DateTable[Year] ).....
This way you'll get your all-time best/worst in the total row of a table/matix.
This has indeed been a very productive thread and hopefully it is now concluded!
Then again @KHorseman will have a chance to respond right after morning coffee... So let see
I did respond right after morning coffee, but not to this thread.
That performance comparison is pretty cool @Sean. Maybe this summarize pattern is more useful than I first gave it credit for. I am definitely going to learn how to use DAX studio this week.
I still want to find a variant of this that returns the arbitrary n-th best/worst. If you can find the first it must be possible to find the second, surely...
Proud to be a Super User!
@KHorseman Yes I saw that response! I should not have mentioned Blank end dates my bad!
Too late now...
It doesn't really apply to the Holiday Table but the powerivotpro example was for promotions and got me thinking in that direction...
Here's a link - explaining some of the features of DAX Studio
Number 4 is Test Performance of your Measures
http://exceleratorbi.com.au/getting-started-dax-studio/
It seems you are on a roll today!
@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...
Proud to be a Super User!
Hi @KHorseman thanks for your solution and the time you have spend for it.
How could I create an index in powerbi? is it possible with dax?
@Sebastian glad it works for you.
Not sure quite what you mean by index. Do you mean an index column? Like a numeric column to give each row a unique identifier? That's easily done in the Query Editor. There's a button among the Add Column buttons that will add an index column. You can start it at 0, 1, or some custom value.
Or are you trying to build the rank table? Just to be clear, that solution will certainly work for you but it's not necessary. If you want a simple solution then the suggestions on the first page of this thread should be more than sufficient for what you originally asked for. That Top Months formula was just something extra I did for fun. But if you do want to use Top Months, I made my RankTable via the Enter Data button. I just typed the numbers into a column and loaded it.
Proud to be a Super User!
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.