I have the below list of data and trying to rank it by sales. My formula is Sales Rank = RANKX[ALL[Sales],[Total Sales]]
The below is the result I get [have used Excel in this example]
I have tried to use Sales Rank = RANKX[ALL[Sales[Client]],[Total Sales]] which works properly but it doesn't work when I change the pivottable rows to Sector. Is there a reason why I can't use ALL on the entire table?
ClientSectorSalesAF&B125BTech456AF&B98BTech546CTech564CTech132DAuto189EFinancial456EFinancial542EFinancial652EFinancial745FEnergy897FEnergy564FEnergy132GF&B52GF&B54GF&B87GF&B98GF&B132
Thanks All!
Message of 7
9,009 Views
1 ACCEPTED SOLUTION
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Report Inappropriate Content
02-05-2020 06:20 PM
Hi @swongbg ,
Please update your formula as below.
Measure = RANKX[ALL[Sales[Client]],[Total sales],,DESC,Dense]
Community Support Team _ Frank If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Message of 7
8,958 Views
- All forum topics
- Previous Topic
- Next Topic
6 REPLIES 6
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Report Inappropriate Content
02-05-2020 06:20 PM
Hi @swongbg ,
Please update your formula as below.
Measure = RANKX[ALL[Sales[Client]],[Total sales],,DESC,Dense]
Community Support Team _ Frank If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Message of 7
8,959 Views
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Report Inappropriate Content
02-05-2020 10:50 PM
Thanks for your help. If I want to rank total sales by sector, I need to write another DAX formula as below?
Measure = RANKX[ALL[Sales[Sector]],[Total sales],,DESC,Dense]
Message of 7
8,942 Views
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Report Inappropriate Content
02-04-2020 10:52 PM
In case you take all
Rank = RANKX[all[Sheet1[Client]],CALCULATE[SUM[Sheet1[Sales]]],,DESC,Dense]
Rank will change with another field you add and give a ranking of the client inside that group.
What is expected outcome
Message of 7
8,999 Views
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Report Inappropriate Content
02-05-2020 05:26 PM
Hi
I am just trying to understand why Sales Rank = RANKX[ALL[Sales],[Total Sales]] doesn't work
Can I not just take the entire sales table?
If I wanted to group by sector, I need to write another DAX formula like: Sales Rank = RANKX[ALL[Sales[Sector]],[Total Sales]]
What is ranked value?
Ranking items identifies their relative position to help you compare data. For example, you can rank a product line, such as golf equipment, to identify how well sales for golf equipment performed relative to other years.nullRank Values - IBMwww.ibm.com › docs › cognos-analytics › topic=calculations-rank-valuesnull
How do you use rank function?
\=RANK[number,ref,[order]] The RANK function uses the following arguments: Number [required argument] – This is the value for which we need to find the rank. Ref [required argument] – Can be a list of, or an array of, or reference to, numbers.nullRANK Function - Formula, Examples, How to Use Rank in Excelcorporatefinanceinstitute.com › resources › excel › rank-functionnull
What is ranking strategy?
Ranking strategies are powerful tools to ensure your best-fitting products are shown at the top of your results to increase clicks and conversions.nullRanking Strategies | Site Search 360 Docsdocs.sitesearch360.com › ranking-strategiesnull