Building a dynamic “Top N” Group and “Other” based on Last 12 Months Revenues

jaquatrezz

New Member
Joined
May 13, 2019
Messages
1
Hello dear Power Bi Community,

I'm pretty new into Power BI, so please excuse if I might need some pretty basic help here. I was trying to find some help here already and eventhough similar posts exist, I didn`t find anything yet which would cover my specific needs.

I would like to automate the following process somehow:
Lets say Im getting every week the following report showing revenues:

Client Period1 Period2 Period3 Period4 ...... Period99
A
B
C
D
E

So every week, the Table would get +1 in columns and +x in rows.
Im already using the "unpivot" function to get a flatflile with one row per client per period (Already this feature is extremly helpful. But then Im ending up copying that flatfile back into an excel and building charts based on sumifs.
Here is where I would like to bring it a step further. My goal would be to show in Power BI Charts for the Top 10 Customers, while Top10 would be based on the highest revenues in the last 12 periods (LTM). So I would somehow have to filter out all the LTM periods and then sort them by size, get the top 10 out of it and group all other clients as "other". I feel its not a super difficult thing to do with the powerful tools in PBI, but somehow I`m still a little confused on when to use a M or DAX solution, or if any coded solution at all. It would be really helpul if you could share your thoughts on this topic with me on how to best achieve that.

Thanks a lot in advance!
Cheers
TK
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
Here is something that might get you going.
The code removes all of the period columns earlier than the latest 10, so you can just work with required data clumns
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    colNames = List.Skip(Table.ColumnNames(Source),1),
    keepLatest10 = Table.RemoveColumns(Source, List.Range(Table.ColumnNames(Source), 1,List.Count(colNames)-10)),
    unpivotByClient = Table.UnpivotOtherColumns(keepLatest10, {"Client"}, "Attribute", "Value")
in
    unpivotByClient
THis code assumes that there is only one column other than the periods, if not adjust the columns skipped.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,992
Messages
5,471,848
Members
406,789
Latest member
gkfcosta

This Week's Hot Topics

Top