Ranking on columns from different dimension tables

Power BI
1

Hi guys
I’m sure this should be an easy one but proves to be elusive.

I’m trying to rank PBT by Business Unit and Dept, but cannot get it to function.
Measure needs to be dynamic to cover off date, Business Unit and dept slicers.
Business Unit and Dept are in separate tables

I understand the issues and have had numerous attempts all ending in failure.
Reading suggests:

  • Use a cross join of the selected columns from the different tables.
  • Sort by columns come into play according to SQL BI, but even if I include the sort by columns in the measure it still doesn’t work. But it does get rid of the dreaded col of 1s.
  • Reza Rad suggests a ConcatenateX of the measure being ranked to assist in seeing what is included in the filter

Tried all of that but getting nowhere fast
MultiColumnRank.pbix (166.4 KB)

Help
Pete

2

Hello @BINavPete,

Thank You for posting your query onto the Forum.

I’m providing two solutions to achieve the results based on the condition that you’ve specified. Below are the measures alongwith the screenshot of the final results provided for the reference -

1). Ranking Results Based On Tabular Form -

Tabular Ranking - Harsh = 
IF( ISBLANK( [Total Sales - PBT] ) , 
    BLANK() ,
IF( ISINSCOPE( Companies[BusinessUnit] ) || ISINSCOPE( Depts[DeptName] ) ,
    RANKX(
        CROSSJOIN(
            ALL( Companies[BusinessUnit] ) , 
            ALL( Depts[DeptName] ) ) , 
        [Total Sales - PBT] , ,
        DESC ) ) )

Final Ranking Results Based On Tabular Form
Final Ranking Results Based On Tabular Form697×848 20.1 KB

2). Ranking Results Based On Hierarchial Form -

Hierarchial Ranking - Harsh = 
VAR _Business_Unit_Ranking = 
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            Data , 
            Companies[BusinessUnit] ) , 
        "@Business_Unit_Ranking" , 
        RANKX( ALL( Companies[BusinessUnit] ) , [Total Sales - PBT] , , DESC ) ) , 
    [@Business_Unit_Ranking] )

VAR _Department_Name_Ranking = 
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            Data , 
            Depts[DeptName] ) , 
        "@Department_Name_Ranking" , 
        RANKX( ALL( Depts[DeptName] ) , [Total Sales - PBT] , , DESC ) ) , 
    [@Department_Name_Ranking] )

VAR _Results = 
SWITCH( TRUE() , 
    ISINSCOPE( Depts[DeptName] ) , _Department_Name_Ranking , 
    ISINSCOPE( Companies[BusinessUnit] ) , _Business_Unit_Ranking , 
BLANK() )

RETURN
_Results

Final Ranking Results Based On Hierarchial Form
Final Ranking Results Based On Hierarchial Form643×865 18 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Important Note: In the case of ties, I’m providing the links of the YouTube videos which are being created by our Enterprise DNA Experts. You can implement that technique as per your requirements in your scenario.

Thanks and Warm Regards,
Harsh

Ranking - Harsh.pbix (75.3 KB)

3

Perfect - thanks @Harsh

4

Thanks for the support @Harsh!!

Hi @BINavPete, did @Harsh solve your query? If so, kindly mark his answer as solution. Thanks!

5

Hello @BINavPete,

You’re Welcome!!!

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh