Rank x

[JamesBurke]

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(COUNTX('Emporia Devices', 'Emporia Devices'[Device name KP]),FILTER('Date',[Measure 12] = 1))

 Just struggling , i'm looking at having a count of amount a store was rank 1 or 2 for example , i'm aware these measures maybe wrong to do this specfic task.

But i want to look at the amount of times a store was rank one by Month ( which is in a seperate table)

Desired result : 

Store Name	Count of rank 2	Count of Rank 3	Count of Rank 4
KP Lymm	0	0	5
KP Macclesfield	5	0	0
KP Henely 1	0	0	0
KP Blackrod	0	5	0

Any Help would be appericated

Thanks , James. 

[JamesBurke]

Hi both , 

@v-yifanw-msft , @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]

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]

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]

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]

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]

please explain what you expect instead

[JamesBurke]

@lbendlin 

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.

[lbendlin]

The sample data you provided doesn't match that.  Not sure how I can help - can you resubmit your sample data?

[JamesBurke]

HI @lbendlin , 

Sample Data.xlsx

My data has dates where there are no values so the Stores are always Rank 1 By default 

Thanks , James

[lbendlin · Accepted solution]

Not sure how this is any better?

[JamesBurke]

@lbendlin 

This is the current issue im having , in that it ranking Months that have no usages attached to them so it's Rank 1 for MOnth's that have no data , which is why in above you have 15 rank 1 when there is only 4 Months of Data

[JamesBurke]

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.

[v-yifanw-msft]

Thank  @lbendlin  for your prompt reply.

Hi @JamesBurke  ,

Depending on the information you have provided, I created a sample data to help you with your problem. You can follow these steps below.

Add new measures:

Count of Rank 2 = 
VAR _count2 =
    CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[Measure 12] = 2 ) )
RETURN
    IF ( _count2 <> BLANK (), _count2, 0 )

Count of Rank 3 = 
VAR _count3 =
    CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[Measure 12] = 3 ) )
RETURN
    IF ( _count3 <> BLANK (), _count3, 0 )

Count of Rank 4 = 
VAR _count4 =
    CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[Measure 12] = 4 ) )
RETURN
    IF ( _count4 <> BLANK (), _count4, 0 )

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...