RANKX measure across two tables not working

[christinamarie]

I currently have two tables:

1. Sales Data, in "long" format, which lists the Week, Salesman name, the Product and the number of Units of each product that person sold.  

2. Photo URLs, a table that contains the Salesman name and a photo URL of that salesman. 

Table: Sales Data

Table: Photo URLs

My goal is to have 3 separate table visuals for the 3 different products (Computers, TVs, Phones), listing the Salesman, the average number of units for that product sold, and that salesman's rank (average units). I then want to have an Image Viewer visual above each table, showing the photo of the salesman with the #1 ranking for each product. I also have a "Week" slicer that dynamically changes the rankings, depending on which weeks of data the user has elected to see. Right now, I have calculated the measure Rank within the Sales Data table using the following DAX expression:

Rank = RANKX(ALL('Sales Data'[Salesman]) , CALCULATE(AVERAGE('Sales Data'[Units])))

The above expression seems to work perfectly for the dynamics of the table visuals and Week slicer. However, when I try to set up the Image Viewer visuals to display the image of the salesman with the Rank equal to 1 and Product to the relevant product, it doesn't work the way I had anticipated, as it shows the same salesperson, regardless of their ranking. The two tables currently have an active one-to-many relationship, joined on the Salesman name and the cross-filter direction is set to "Both". 

The Ranks corresponding to each picture are all 1, regardless of the average number of units.

To see what the issue was, I set up an additional table visual that contained the Salesman Image URL, the Average Units Sold and the Rank measure, where Product = 'Computers'. All of the Ranks equaled 1, even though the averages themselves weren't equal. Any help to get the images to dynamically change with the use of the Week slicer would be greatly appreciated!

[OwenAuger · Accepted solution]

Hi @christinamarie

On your Image Viewer visuals, are you applying a Visual Level Filter set to Rank = 1?

If so, you should be able to fix your issue by adjusting your Rank measure:

Rank =
RANKX (
    ALL ( 'Sales Data'[Salesman] ),
    CALCULATE ( AVERAGE ( 'Sales Data'[Units] ), ALL ( 'Photo URLs' ) ),
    AVERAGE ( 'Sales Data'[Units] )
)

The problem looks to be due to the Photo URLs filter being present when RANKX iterates through ALL ( 'Sales Data'[Salesman] ), so the above measure clears the 'Photo URLs' filters for that purpose.

Alternatively, rather than using Rank as a Visual Level Filter, you could add either 'Photo URLs'[Salesman] or 'Sales Data'[Salesman] as a Visual Level FIlter on the Image Viewer, with Filter Type = TopN, set to "Show Top 1", with By Value = Units (average).

Do either of these work?

Post back if needed.

Regards,

Owen

[christinamarie]

@OwenAuger

This worked perfectly, thank you so much!