Forum Discussion

Kitkat's avatar
Kitkat
Frequent Visitor
8 years ago
Solved

Summarize and Rank by multiple columns and rows

Total Revenue Client Name Year Month Revenue Stream -1625.7 ABC 2016 10 1 -11693.9 DEF 2016 10 2 -598.79 GHI 2016 10 3 -523.52 JKL 2016 10 1 -84.85 MNO 2016 ...
  • Kitkat's avatar
    Kitkat
    Frequent Visitor
    8 years ago

    I have tried the following function:

    Summarize

     Please advise where my error is

    Thanks kindly and truly appreciate the help

    Cheers

    Kat

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      8 years ago

      create a summarize table, go to modelling tab, click new table and add following:

       

      TableSummarize = SUMMARIZECOLUMNS(Table1[Client Name], Table1[Revenue Stream], "Total Revenue", SUM(Table1[Total Revenue]) )

      Now in above table, add new calculated field for rank:

       

      Rank = RANKX(Filter(TableSummarize, TableSummarize[Revenue Stream] = EARLIER(TableSummarize[Revenue Stream])),TableSummarize[Total Revenue],,DESC,Dense)
      • Kitkat's avatar
        Kitkat
        Frequent Visitor
        8 years ago

        Hi parry2k- Thank you kindly the summarize table worked, however for my Rank all i get is a #1 for each- im trying to get top 50 clients within each stream by revenue, do i use asc,Skip instead?

  • Kitkat's avatar
    Kitkat
    Frequent Visitor
    8 years ago

    Hi v-chuncz-msft- greatly appreciate you pointing me in right direction and more information for the toolkit.

    Cheers

    Kat

     

  • Kpavanteja91's avatar
    Kpavanteja91
    Regular Visitor
    7 years ago

    Rank = RANKX(CROSSJOIN(ALLSELECTED(Table[Client Name]),ALLSELECTED(Table[Revenue Stream])),CALCULATE(SUM(Total Revenue)))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I need Top N Locations based on their sum of scores and ordered by the sum of Total Loss amount, and want to the locations to be ranked for the records where their sum of Toptal Loss amount is not blank, I am able to get the ranked locations based on sum of Total Loss amount but not able to get their sum of scores(of Ranked locations), I am getting al the locations, please help.

    Measure =
    var RankingDimension = values('Table'[Location name])
    Var
    RankingSelect = selectedvalue('Top N RG'[Top N RG])
    var ranking2 = if(isblank(calculate(sum('Table'[Total Loss]))), blank(),
    rankx(filter(All('Table'[Location name]), not(isblank(calculate(sum('Table'[Total Loss]))))),
    calculate(sum('Table'[Total Loss])),, desc,skip))
    return
    ranking2