Help with RANKX based on another RANKX ?

[Tad ElamParticipant Started: 1Replies: 5Total: 6]

Hi there – first time user, first time poster 🙂

I have two measures created (KEY_ACCOUNT_CALC and GROWTH_ACCOUNT_CALC) – these measures are simply dollar values.  I use these measures then, to create Rankings (KEY_ACCOUNT_RANK and GROWTH_ACCOUNT_RANK) simply, with RANKX(ALL(Account[Customer]),[Key Account Calc]) and then again, but with growth accounts.

In my spreadsheet I have two tabs – one for Key accounts which has stats on the top 15 Key accounts – no problem.  on the next tab though – I want to show the top 15 Growth accounts.  Here’s where I run into issues:  If any of the top 15 Growth accounts are also Key accounts I do not want them in the top 15 Growth accounts.  does that make sense?

Both of these formulas yield the resulting screenshot, attached:

TAD:=RANKX(TOPN(15,FILTER(ALL(Account[Customer]),[Key Account Rank]>10),[Growth Account Calc]),[Growth Account Calc])

TAD:=RANKX(FILTER(ALL(Account[Customer]),[Key Account Rank]>10),[Growth Account Calc])

Thanks in advance – of course if you need more info please ask 🙂

Tad Elam

 

 

 

Attachments:

You must be logged in to view attached files.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hi Tad,

Do the rankings reflect more of a static status (not intended to respond to slicers) of customers, at least not until data is refreshed?

If so, you could assign rank in a calculated column for key accounts, and then in a separate calculated column rank growth accounts, but filter out the rows from key accounts.

Let me know if your rankings should respond to slicer selections.

Tom

[Tad ElamParticipant Started: 1Replies: 5Total: 6]

Hi Tom – thank you for the reply.

Currently there are no slicers involved.  That being said, the pivot is summarizing multiple “Ship-To” accounts (Each Bill-To account can have multiple Ship-To’s).  This means I can’t put a static calculated column in, as they would all be ranked 1, right?

This is my first real foray into DAX, so I’m a little baffled by the structuring.  I’m more used to building SQL tables with exactly what I need and then using that.  I do see the HUGE value though, in using DAX, so I’m excited to get more familiar with it.

I have attached another screenshot to try and explain better.  I wonder if you would consider a call and a screen sharing session so I could show you exactly what I’m dealing with?

Thanks in advance ,

Tad

Attachments:

You must be logged in to view attached files.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Tad,

Regarding the phone call and a screen sharing session, that would fall under the paid services of PowerPivotPro, which you can read about here.

Forgive me though, for not taking a closer look at your formula:

TAD:=RANKX(TOPN(15,FILTER(ALL(Account[Customer]),[Key Account Rank]>10),[Growth Account Calc]),[Growth Account Calc])

I think your issue may be resolved if you change the 10 to a 15 when comparing [Key Account Rank]:

TAD:=RANKX(TOPN(15,FILTER(ALL(Account[Customer]),[Key Account Rank]>15),[Growth Account Calc]),[Growth Account Calc])

Let me know if that fixes the issue.
—–
A comment or two:

Do not be discouraged with DAX’s learning curve. Your formulas are well thought out and definitely reflect experience.

On the comparison of SQL and DAX, many of the differences stem from that DAX is designed as a functional language and SQL is designed to be (with a few exceptions) a declarative language. Also, there are some conceptual differences such as when I use a GROUP BY for a SUM in SQL to get a total, I visualize that values in rows are just added together, but totals in “power pivot” pivot tables are controlled by removing filters — in a cell of a “power pivot” pivot table there is no awareness of any other cell, let alone of any other row.

For me, the game changer for getting a handle on DAX was taking Rob’s Comprehensive On-Line Power Pivot Course. It does distill years of Rob’s learning experience into about 22 hours and there are about 45 workbooks with demos, many containing multiple worksheets. The price may seem “not cheap”, but it is highly cost effective. Let me give an example: before taking the course, I had paid someone else $100 USD to quickly teach me an advanced topic in Power Pivot (and felt that was worth the investment), but when I took Rob’s course that was just “one” many topics covered.

Also, important in Rob’s course is getting the right handle on the learning curve: although the topics move from simple to advanced, the focus is on growing in understanding and power while removing unnecessary complexity.
—–
Well, ’nuff said for now.

I am still convinced the fix is just changing the number 10 to 15 when comparing [Key Account Rank]: there are 5 lines that should not be there (see 2, 5, 6, 14, 15 under Key Account Rank in the first image).

Tom

[Tad ElamParticipant Started: 1Replies: 5Total: 6]

I appreciate the support very much, thanks.  I have sent the Live training info to my boss, hopefully he will allow me to attend the session in Indiana this spring.  I am also looking at the course you mentioned.

changing it to 15 did make it change, but did not correct the issue (see screen shot).  The Key Ranks of 2, 5 and 6 are still there 🙁

the way I read the formula: RANKX(FILTER(ALL(Account[Customer]),[Key Account Rank]>15),[Growth Account Calc]) in English would be

FILTER: grab all accounts with Key Account Rank > 15 and then RANKX: rank them by Growth Account Calc.

I don’t see any other way to read that, do you?  It’s just not filtering out the Key Account Ranks like we’re telling it to…  Also I’ve removed the TOPN call, which means it would take whatever (10 or 15) that I’m passing, but that yields the same results.  I’m completely baffled by this one.  Can you think of anything else I can try?

Thanks,

Tad

Attachments:

You must be logged in to view attached files.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Tad,

There is another way to read that, starting with: the ALL ( Account[Customer] ) is the only column where the external filter context is entirely ignored. Let me chew on that for a little while.

There is another approach to take, but let’s hold off on that for a little.

Tom

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Without seeing all of your model, I am convinced that there is a physical relationship between Account and Sales, so I am disregarding the possibility that there is not a relationship there, but if the Sales table also has a Customer column and that is on the pivot’s Rows drop zone, that could be a issue.

If you would double check and make sure that Account[Customer] is on the Rows drop zone…

Tom

[Tad ElamParticipant Started: 1Replies: 5Total: 6]

Yes the Accounts and Sales tables are related by Account ID.  I also verified the Customer field on the pivot is coming from the Accounts table.

Is it possible to use the RANKX(FILTER(xxx…. formula without using ALL()?  Every different way I try this formula and it just seems to hang on Reading Data…  I have waited 5+min and it doesn’t finish, I have to Esc to Cancel.

RANKX(FILTER(Account,[Key Account Rank]>15),[Growth Account Calc])

Furthermore:  I am using column filters – filtering out some Customers (b/c they aren’t actually customers), so my understanding is using ALL removes that filter.  Could that be having some effect on the formula?  I’m just throwing everything in the pot now, I’m thoroughly confused.

I have attached yet another screen shot – this time I removed all filters, and using my original formula of:

RANKX(FILTER(ALL(Account[Customer]),[Key Account Rank]>10),[Growth Account Calc])

I’m seeing duplicate ranks, just like before – even though the GROWTH_ACCOUNT_CALC numbers are not equal…  confused… and a little frustrated at this point. :(…   the FILTER isn’t working (because Key 7 is there, and the RANKX isn’t working (because there are dup’s when there shouldn’t be dup’s).

 

Attachments:

You must be logged in to view attached files.

[Tad ElamParticipant Started: 1Replies: 5Total: 6]

Oh by the way – I registered for the anytime course.  I will be viewing that in my spare time.  I already have the first DAX Formulas for PP book by Rob, so the 2nd Edition will be a nice addition to my collection 🙂

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Tad,

Let’s try this as a possibly helpful diversion: the next time you log into the course, there should be a text-box available where you can enter a question for the instructor. Enter a question and I think it will get routed to me (if so, I will respond to you with my powerpivotpro email address). OK?

Then I have something else we can look at.

Tom

[Tad ElamParticipant Started: 1Replies: 5Total: 6]

Thanks Tom – I sent the email as you requested.

[Author]

Posts