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
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

     

     

     

     

    • 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

       

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

        please explain what you expect instead