Multi-Variate Ranking

[amotto11]

Hi all,

I have searched through forums and have tried for days/weeks to solve this problem to no avail. I am going to do my best to illustrate what i am trying to accomplish, but please let me know if something is confusing.

The table that i am working with right now is structured as follows:

PolicyID - Individual quote number

RateID - company by quote number

RateVehicleID - vehicle by company by quote

RateVehiclePremID - coverage by vehicle by company by quote number (basically this is a unique ID for this table)

CoverageID - coverage ID

Premium - amount the company will charge by coverage by vehicle by quote

Below is a subset of data with the rankings (this is what i need help on) that i would like to see:

PolicyID   RateID   RateVehicleID   RateVehiclePremID   CoverageID   Premium   RankingTotal

1                 1                  1                             1                         1                100              2

1                 1                  1                             2                         2                200              2

1                 1                  2                             3                         1                250              2

1                 1                  2                             4                         2                250              2

1                 2                  1                             5                         1                300              1

1                 2                  1                             6                         2                100              1

1                 2                  2                             7                         1                250              1

1                 2                  2                             8                         2                100              1

2                 1                  1                             9                         1                 50               3               

2                 1                  1                            10                        2                 75               3

2                 2                  1                            11                        1                 60               1

2                 2                  1                            12                        2                 25               1

2                 3                  1                            13                        1                 20               2

2                 3                  1                            14                        2                 75               2

This is only 2 quotes, but the data i am working with has 50,000 quotes, each having around 10 companies (not all the same on every quote), and 8 coverages. I think i can do the ranking shown, but the problem comes when i want to limit the CoverageID to just a subset and/or limit the RateID to just a subset using slicers, the ranking function will need to accomidate this.

Any help is greatly appretiated.

[Stachu]

if you want the rank to be dynamic I think it's better to use measure rather than calculated column
what is the basis for rank? SUM of [Premium]?

[amotto11]

Thank you for your response. I am tring to use a measure, but i was trying to illustrate how the measure should work, so i apologize if it came accross as a calc column. The basis for the rank is sum of premium, but i want to include/exclude different companies/coverages and have the rank change. In my dashboard i am checking the accuracy of the measure by basically rebuilding the table and selecting a handful of policies to make sure the ranking function works properly, but in the end i will be showing this data differently. I will be showing the data by company and how many times they are ranked number 1 vs number 2 etc. The slicers i want are by company and by coverage so that i can choose to only compare certain companies on certain coverages and get their summarized rankings. I hope i am explaining this well, but if not, please let me know if you have any questions.

[Stachu]

would something like this work:

Rank:=RANKX(ALLSELECTED('Table'[RateID]),CALCULATE(SUM('Table'[Premium])))