Solved

RANK X - Rank products by revenues - Powerpivot (DAX)

Posted on 2016-07-29
6
40 Views
Last Modified: 2016-07-29
Attached is a spreadsheet containing a sales transactions table. On the sales by part sheet you will see I have created a simple pivot table from a PowerPivot model. I would like to create a measure that will rank the part codes by revenue.

Can anyone help me with a measure and explain the basic logic behind it.

I am novice with DAX but would like to learn more so the logic helps.

Thanks
Mike
0
Comment
Question by:mikes6058
  • 3
  • 3
6 Comments
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41734523
HI Mike - no attachment.
0
 

Author Comment

by:mikes6058
ID: 41734527
Sorry Rob
trans_B.xlsx
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 41734532
In your pivot you can simply drag the salestotal into the value field again and then change the settings on the second occurence to show Rank.

In the Field List Navigation pane. Drag SalesTotal to the bottom right pane. Click on the drop-down option for the second occurrence and choose Value Field Settings. Partway down the window there are two page tabs, choose "Show Values As".  Then choose the drop-down list just below the tabs. In this list there are a couple of options for Rank, Small to large and Large to Small. Large to Small will rank the highest value as number 1.

Thanks
Rob H
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:mikes6058
ID: 41734545
Thanks Rob,

This would work in this isolated scenario however going forward I would like to be able to view rankings by sub groups.

For example number 1 and 2 rank for a particular product group.

Would you be able to provide the DAX solution?

I'm also working towards a solution similar to the one explained in the link below.

 http://www.powerpivotpro.com/2014/11/displaying-top-n-bottom-n-and-all-others/

Mike
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41734555
Sorry, don't know anything about DAX.
0
 

Author Closing Comment

by:mikes6058
ID: 41734561
No problem, points for the alternative solution.

btw, I will be posting a number of other questions that won't involve DAX

Mike
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now