• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 140
  • Last Modified:

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

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
mikes6058
Asked:
mikes6058
  • 3
  • 3
1 Solution
 
Rob HensonIT & Database AssistantCommented:
HI Mike - no attachment.
0
 
mikes6058Author Commented:
Sorry Rob
trans_B.xlsx
0
 
Rob HensonIT & Database AssistantCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mikes6058Author Commented:
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
 
Rob HensonIT & Database AssistantCommented:
Sorry, don't know anything about DAX.
0
 
mikes6058Author Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now