Results 1 to 2 of 2

Thread: Building a dynamic “Top N” Group and “Other” based on Last 12 Months Revenues
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular
    Join Date
    Jan 2012
    Posts
    844
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •