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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

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,101,925
Messages
5,483,735
Members
407,406
Latest member
ishipra

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top