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

      • JamesBurke's avatar
        JamesBurke
        Helper III
        2 years ago

        Hi lbendlin , 

         

        It's just the first value if you look below 

         

        Ranked = 
var s = SELECTEDVALUE('Emporia Devices'[Device name KP])
var a = ADDCOLUMNS(ALL('Date'[Mth & Yr]),"kwh",CALCULATE(sum('Emporia Device Usage'[Usages Kwh]),'Emporia Devices'[Device name KP]=s))
var b = ADDCOLUMNS(a,"r",var d = [Mth & Yr] return [Max Month Rank])
return 
countrows(filter(b,[r]=SELECTEDVALUE(Ranks[Value])))

         

        On the Left is all the MTH & YR where there are Data Hoever it says180 + Rank 1's ,  

         

           CALCULATE(
        MAX('Date'[Year Month Number]),
        FILTER('Date',[UsagesCount] >= 1),
        ALL('Date'))
        UsagesCount = CALCULATE(COUNTROWS('Emporia Device Usage'))

        This filters all date to months only where are data but i'm not sure how to implement this into the Measure. 

         

        Thanks , James.