Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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!
ah, ok.
i meaned the column "Monthindex" in your DateTable. You used it for the calculation of the bottom months.
Oh yeah. That part should actually be in all of those formulas. Top Months, Bottom Months, Top Sales, Bottom Sales... I'm just using it as a forced tiebreaker in case two months ever match exactly. It's optional. I think if you skip that part of the pattern it will basically just choose randomly if any two months tie.*
Anyway the formula for it is in the query for DateTable in that example file I linked previously. Go to Edit Queries and it's the 12th step. You're welcome to steal the entire query if you like. The source step is where you set the start date and the total number of dates covered by the table to whatever suits your needs. Just open the advanced editor in the query and copy and paste the whole thing into your own blank query. But if you just want to add a month index to your own query the formula is:
=if [Index] = 1 then 1
else if [Date] = Date.StartOfMonth([Date]) then
List.Count( List.Distinct( List.FirstN( #"Added Month of Year"[Month of Year], [Index] - 1))) + 1
else List.Count( List.Distinct( List.FirstN( #"Added Month of Year"[Month of Year], [Index] - 1)))
...which in English translates to, "Put a 1 on the first row. After that, check to see if the date is the start of a new month. If it is, add 1 to the value in the previous row. If not just copy the previous row." [Index] is just a regular index column starting at 1 as I described in my last reply. I don't know how to do the equivalent in DAX, or even if it's possible in DAX. Of course you can do it in DAX. Duh. See the next post in this thread.
* for bonus points, consider how the basic pattern sorts things. You could either add or subtract 1/[MonthIndex] as a tiebreaker value. Depending on whether you add or subtract, it will mean that earlier months rank either higher or lower. To be consistent you may need to perform the opposite operation on Top Months than you do on Bottom Months, because one sorts by how low the value is and the other sorts by how high it is.
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!
@Sean re-download the example file. I just uploaded a new version with extra stuff a minute ago.
Proud to be a Super User!
I'm not sure I have enough context to answer, but possibly the problem is that the setup on the left has years as row context? Table visuals sometimes seem to get weird when you try to sort them by a measure. If you have more than one naked column showing on the table it will just sort within each outer row context. Try filtering that table on the left to only show 2012. The overall top 4 are all in that year so with that filter you should get matching results for those 4. If so, it's just table visuals being table visuals. If not, something really is wrong.
Edit: we have got to stop editing so much. We both talk over each other's edits. 😛
Proud to be a Super User!
@KHorseman Yes! Sorry about that...
My table on the left is within each YEAR Best (Previous Measure) and the New Measure ranks top 5 best of all time!
Obviously they can be diffrent!
EDIT: Again @KHorseman really great solution!
Here's my version... maybe slightly easier to read - DESC instead of 0 and ASC instead of 1 and spelled out the Row Test Measure
Great job!
Top Months Z = FIRSTNONBLANK ( SUMMARIZE ( TOPN ( 1, TOPN ( MIN ( RankTable[Rank Column] ), SUMMARIZE ( SalesTable, 'Calendar'[Year-Month], "Monthly Sales", SUM ( SalesTable[Value] ) ), [Monthly Sales], DESC ), [Monthly Sales], ASC ), [Year-Month] ), 1 )
@Sean well I did say we both do it.
This pattern is pretty flexible. If you want years, switch the visual to a matrix. Drop Year and Rank into the rows well and you'll get Top Months and Bottom Months and whatever else per-year with no adjustments to the formulas.
Edit: yeah that rewrite is much more readable. Good work to you too.
Proud to be a Super User!
@KHorseman I will disqualify myself from the quiz since I've already done it
But yes the change from one to the other comes to exactly this!
Have not tried the SUMMARIZE solution yet but I'll give a go later today...
And just because I'm trying to learn how the SUMMARIZE() function works*, here's a kind of goofy method of getting the same results. For this one you don't need any other supplemental measures, but you do still need the date table.
Alt Worst Month = IF(
HASONEVALUE(DateTable[Year]),
FIRSTNONBLANK(
SUMMARIZE(
TOPN(
1,
SUMMARIZE(
SalesTable,
DateTable[Month],
"Monthly Sales",
SUM(SalesTable[value])
),
[Monthly Sales],
1
),
[Month]
),
1
),
BLANK()
)
*and because it's Friday and this is the sort of thing I consider slacking off at work...
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
117 | |
109 | |
109 | |
93 | |
69 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
94 |