Ranking the same for each client based on business line

DAX DAX Calculations
1

Hi guys,

I want to rank the same number for each client based on business line as part of a dynamic dax measure.

I’ve worked out how to do it with a table created by a dax measure but when I do the same in a virtual table, incorrect results are returned. The tables on the right show correct results but the table on the left shows incorrect results, all rankings are number 2, regardless of business line:

image
image1064×703 55.5 KB

My PBI working file is here, sheet “ranking test”
5 Hierarchy Bar Chart.pbix (312.3 KB)

The DAX for my calculated table is this, it returns correct results:

dim_Clients With Ranking = 

VAR __tbl =
    
    ADDCOLUMNS(
        ALLSELECTED( dim_Contractors[Type of business line #1]),
        "@amount",
        [Revenue]
    )

VAR __rank =
    
    ADDCOLUMNS(
        SUMMARIZE(
            dim_Contractors,
            dim_Contractors[Client name v2],
            dim_Contractors[Type of business line #1]
        ),
        "Ranking",
        RANK(
            DENSE,
            __tbl,
            ORDERBY( [@amount] , ASC , dim_Contractors[Type of business line #1], ASC)
        )
    )

RETURN
    __rank

and then I just add a simple MIN and I create my visual with ranking based on business line:

MIN('dim_Clients With Ranking'[Ranking])

but when I do the exact same thing with a virtual table, incorrect results are returned:

VAR __tbl =
    
    ADDCOLUMNS(
        ALLSELECTED( dim_Contractors[Type of business line #1]),
        "@amount",
        [Revenue]
    )

VAR __rank =
    
    ADDCOLUMNS(
        SUMMARIZE(
            dim_Contractors,
            dim_Contractors[Client name v2],
            dim_Contractors[Type of business line #1]
        ),
        "Ranking",
        RANK(
            DENSE,
            __tbl,
            ORDERBY( [@amount] , ASC , dim_Contractors[Type of business line #1], ASC)
        )
    )

VAR __minx =
    
    MINX(
        __rank,
        [Ranking]
    )


RETURN
    __minx

Thanks,
Tim

2

@Timmay ,

I think this should do the trick:

Rank Bus Line = 
VAR __vTable =
    ADDCOLUMNS (
        SUMMARIZE (
            ALL ( FCT_table_Contractors ),
            dim_Contractors[Type of business line #1],
            dim_Contractors[Client name v2]
        ),
        "@TotRevenue", [Tot Revenue All Clients]
    )
VAR __Rank =
    RANK ( DENSE, __vTable, ORDERBY ( [@TotRevenue], DESC ), LAST )
RETURN
    __Rank

Tot Revenue All Clients = 
SUMX ( ALL ( 'dim_Contractors'[Client name v2] ), [Revenue] )

![image|460x500](upload://eUYG451GYn2TNkEuocFLTNXge99.png)


[5 Hierarchy Bar Char_BEJ Solutiont.pbix|attachment](upload://aDkyHvendBOxNMbxAYMCrxtg8xT.pbix) (313.4 KB)

Hope this gets you what you needed ..```

- Brian
2 Likes
3

Solved with beautiful simplicity, just needed to approach the problem from a different angle, thanks once again Brian.

1 Like
4

@Timmay ,

Great! Glad that got you what you needed. You were close - sometimes it just helps to have fresh eyes on a problem after you’ve been banging away at it for a long time.

  • Brian