Forum Discussion

JamesBurke's avatar
JamesBurke
Helper III
2 years ago
Solved

Rank x

Hi All,      Measure 12 = CALCULATE(RANKX(ALL('Emporia Devices'[Device name KP]),CALCULATE(SUM('Emporia Device Usage'[Usages KWH])),,DESC,Dense))       Count of Rank 1 = CALCULATE...
Need Help
Show and Tell
Tutorial Requests
  • lbendlin's avatar
    lbendlin
    2 years ago

    Not sure how this is any better?

     

     

    usage by kwh.pbix36 KB
JamesBurke's avatar
JamesBurke
Helper III
2 years ago

Hi both , 

 

Anonymous , lbendlin 

 

Sample Data.xlsx

 

Attached is what my data looks like (obviously much bigger and more detailed but i have data in seperate tables rather then having usages etc all in one table so when i try to create this rank column im unable too. 

 

Hopefully the sample data helps.

 

Thanks again , James.

lbendlin's avatar
lbendlin
Super User
2 years ago

You want the ranking by month?  Your sample data is not very diverse so you will run into tie issues. DENSE or SKIP?

 

 

Might be easier to use a Ribbon chart - that gives you the ranking for free.

 

 

  • JamesBurke's avatar
    JamesBurke
    Helper III
    2 years ago

    Hi lbendlin , 

     

    It would be ranking by Store Name rather then device ID , the Device id are associated with a store name so it can be broken down by the store rather then the device ID. 

     

    but yes a Month Ranking in which i can count how many times a store has been rank 1 

     

    Thanks, James.

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      OK, you can adjust the formula slightly.

       

      Then you can materialize the result and achieve the desired outcome. You will need a disconencted table for the rank buckets.

       

      Ranks = GENERATESERIES(1,10)

      see attached

       

       

       

       

      usage by kwh.pbix33 KB
      • JamesBurke's avatar
        JamesBurke
        Helper III
        2 years ago

        Hi lbendlin , 

         

        This is what is currently showing and below is the Measure being Used : 

         

        Ranked = 
var s = SELECTEDVALUE('Emporia Devices'[Device name KP])
var a = ADDCOLUMNS(ALL('Date'[Dim Date ID]),"kwh",CALCULATE(sum('Emporia Device Usage'[Usages Kwh]),'Emporia Devices'[Device name KP]=s))
var b = ADDCOLUMNS(a,"r",var d = [Dim Date ID] return rankx(all('Emporia Devices'[Device name KP]),CALCULATE(sum('Emporia Device Usage'[Usages Kwh]),'Date'[Dim Date ID]=d),[kwh]))
return countrows(filter(b,[r]=SELECTEDVALUE(Ranks[Value])))

         

        Not sure where i'm going wrong. 

         

        Thanks , James