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

[Mking4646]

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         4               3                     3                  7

_________________________________________________________________________________________

2nd help 

a task I've been set as part of a PowerPivot creation

1.Enter the last rep to have sold to the customer URN on the query tab of the PowerPivot. Now there's no query tab for me on powerpivot and as far as i'm aware query was part of a ribbon thats now get and transform which has nothing to do with being able to pull that type of information

I'm pretty confident on my measure ability having created many time intelligence formulas but I'm running around in circles with these two with a pounding headache.

I've pulled the customer to the table, created measure to bring in last purchase date, created measure to show days since last measure among many other value based measures filtering by specific types

I really just need the last salesperson name to be populating the the pivot table

please if anyone is able to help

[tamerj1 · Accepted solution]

@Mking4646 

Please try

=
RANKX (
    ALLSELECTED ( Table1[Customers] ),
    CALCULATE ( [Measure], ALL ( Table1[URN] ) )
)

[Mking4646]

HERO!😍

thats worked perfectly with having both in rows!

Thank you very much!

i'll continue to play around with last salesperson bits you mentioned previously and hope i can crack it

[tamerj1]

Hey..
Would you like to join with teams or zoom?

[Mking4646]

that would be incredible please

[tamerj1]

Hi @Mking4646 
Regarding the RANKX is simply RANKX ( ALLSELECTED ( Table1[Customers] ), [Measure] )

You can then sum the for ranks if you wish [Rank1] + [rank2] + ......

Regarding the wrong total you need to simulate the matrix table in order to iterate over it inside the measure. This is an example

=
SUMX (
    SUMMARIZE ( Table1, Table1[Customer], Table1[URN] ),
    CALCULATE (
        IF (
            NOT ISBLANK ( [Date of last purchase] ),
            VALUE ( [Most Recent Purchase] - [Date of last purchase] )
        )
    )
)

You need to summarize the source table by the columns place in the row of the pivot table. This would create a temporary photocopy of the pivot table inside the measure. The CALCULATE provides the required context transition but might not be required in the cases where the code refers only to other measures and has no other calculations or aggregations. Hope this helps a start for your search.

[Mking4646]

Thanks again for trying to help

Thats what i had already been doing but just keeps turning all ranks into 1 like above 😞

[tamerj1]

@Mking4646 

You may try to replace Customer with URN

[Mking4646]

Good Morning! 

So that RankX formula does work but not dynamically. It only works if i have 1 of customer name or URN in the rows tab. I've tried shoehorning an AND/OR Function into it without success

[tamerj1]

@Mking4646 

Try to remove the URN from the matrix ans see what results you get. 

[Mking4646]

Yeah i've removed URN from rows and the ranking works fine. Just was hoping to have the sub headings involved but thats no big issue!

you've been very helpful and solved 2 of my problems, I still haven't cracked that last salespeson measure unfortunately if you're able to offer any more insight there

[tamerj1]

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]

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?