I hope someone can help me with the RANKX. I don’t know it is just me, but I seem to regularly get stuck with RANKX?!
Please see attached sample report using dummy data:
Ranking with additional columns.pbix (183.2 KB)
We need to show in a table, the ranking of Sales by Sales Executive. I have this working in a simple table using the measure below:
Sales Rank for Exec = RANKX (ALLSELECTED ( Sales[Sales Exec] ), [Total Sales], , DESC, DENSE)
However, if I add another column to the table to give our users some additional context, such as the ‘Site’ where the Sales Exec works (from dim table [Sites]), the ranking breaks:
The only solution I have found, is to bring the ‘Site’ column into my Sales fact, using RELATED and then to adjust my ranking measure to do a CROSSJOIN:
Sales Rank for Exec 2 = RANKX ( CROSSJOIN ( ALLSELECTED ( Sales[Sales Exec] ), ALLSELECTED ( Sales[Related Site] ) ), [Total Sales], , DESC, DENSE )
If we are then asked to add another column, again to provide further information to the users, such as Sales[Location Code], the measure needs to be adjusted again to work:
Sales Rank for Exec 3 = RANKX ( CROSSJOIN ( ALLSELECTED ( Sales[Sales Exec] ), ALLSELECTED ( Sales[Related Site] ), ALLSELECTED ( Sales[Location Code] ) ), [Total Sales], , DESC, DENSE )
In reality, we are being asked to output many information columns coming from a few different DIM tables, which will involve lots of Cross joins and many RELATED columns being added to my fact table, which isn’t ideal! Eg:
Is there a better to way to handle this?