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

[jaquatrezz]

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

 

[theBardd]

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

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.