Forum Discussion

Mking4646's avatar
Mking4646
Frequent Visitor
4 years ago
Solved

2 things I need help with please! Ranking Individual Measures in a Powerpivot Table &&

Hello, i'm trying to create new columns on the pivot that display the rank of each individual measure here i.e                      revrank Lengthrank RecencyRank QuantityRank customer 100  ...
DAX Filtering
DAX RANKX
  • tamerj1's avatar
    tamerj1
    4 years ago

    Mking4646 

    Please try

    =
RANKX (
    ALLSELECTED ( Table1[Customers] ),
    CALCULATE ( [Measure], ALL ( Table1[URN] ) )
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi Mking4646 
The first question is not so clear. Do you want to concatenate the values of the four ranks in one cell? Or there is some sort of calculation involved?

 

The answer to the 2nd question depends on your data model. In which table can we find the sales rep.? And what are the relationshipd between the tables. If we assume only one table is involved in this pivot table then you already acheived 90% of the result by retrieving the last date. You just need to calculate the current Customer UR table using ALLEXCEPT then you can filter it to the last date. That would most pobably leave a table of record one record that belongs to the last Sales Rep. In power Pivot you have an option to retrieve the Sales Rep. which is using DISTINCT ( SELECTCOLUMNS (..... However you might get into trouble if you have more than one Sales Rep. existing in the last date table. Then you may consider using CONCATENATEX to retrieve all the Sales Rep. invloved in last day tranactions.

  • Mking4646's avatar
    Mking4646
    Frequent Visitor
    4 years ago

    Hey! Thank you for the reply!

     

    To add more clarity,

     

    it's 1 solitary "table1", i've looked at doing some normalisation to the data model but it's fresh raw data from a field i'm not familiar with so some of the headers etc i can't quite pin and taking too long to normalise when not essential.

     

    so the 1st part with the ranking I have about 1000 customers i want to have 4 seperate 1-1000 ranking scores for that i would use to talk through.

    column for revenue rank, column for customer length rank etc.

     

    I would happily sum the 4 together i.e the lowest spending, shortest customer with lowest purchase number and frequency would rank 1000th on all 4 and recieve a score of 4000. 

     

    part 2 with the sales rep id I just cant quite click it, i'm still fairly new to this but an ok standard with many syntax. heavily profocient in non dax excel formulas which can skew my direction. I feel its simple and i'll have a play with the above mentioned ALLEXCEPT to see how I fare

     

    also since this post i've noticed some of my grandtotals are off. for example I've calculated days since last purchase using 

     

    {{  IF( ISBLANK( [Date of last purchase] ), BLANK(), VALUE( [Most Recent Purchase] - [Date of last purchase] )) }}

     

    with maxx and minx of this for "most recent purchase" and "date of last purchase"

     

    {{  =maxx( ALL(Table1), Table1[Pub_Date])   }}

     

    does that help you narrow down anymore?