Forum Discussion
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(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.
Not sure how this is any better?
16 Replies
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-Forum/ba-p/963216
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/1447523Thank @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.
Hi both ,
Anonymous , lbendlin
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.
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.
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.
