Forum Discussion
Rank x
Not sure how this is any better?
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.
see attached
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
please explain what you expect instead
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.
Expecting Rank 1 to have 4 values and them all be in Kp Henley 1 as it's the only one that has been ranked one.
At the Moment it is counting the 192 rows for all stores
Expected Result :
Value KP Black rod KP Henley 1 KPLymm KP MMacclesfield 1 0 4 0 0 2 1 0 3 0 3 3 0 0 0 4 0 0 0 4 Thanks , James.
The sample data you provided doesn't match that. Not sure how I can help - can you resubmit your sample data?