none
Help with RANKX measure

    Question

  • Hi,

    i have 2 tables in SSAS Tabular that are related by Acct Number.  Here are some snippets of each table with sample data:

    Clients:

    Values:

    so Total Sprinkler Area is the total area (square feet) of the location.  the Sprinkler Needed Area is the total Area (square feet) that is in need of sprinklers.  So the smaller the Sprinkler Needed Area compared to the Total Sprinkler Area, the better the location is protected in case of fires.

    i need to create a ranking measure by Account and BusinessAreaId for each year to rank the Percentage of Sprinkler Area Needed.  I already created a new measure called SprinklerNeededPct:

    SprinklerNeededPct:= DIVIDE(sum(Values[Sprinkler Needed Area]),sum(Values[Total Sprinkler Area]))

    this calculates the Pct of Sprinkler Area needed.  Here is a sample of the results by account and business area:

    so for a ranking in 2018 and Business Area Id = 1, account 11027 should be ranked 1 and account 28365 should be ranked 2.  For 2018 Business Area Id = 2, account 00043 should be ranked 1 and account 00794 should be ranked 2 and so forth for each year.

    The end result will be a power bi report that will be run by account and year and will show what the Sprinkler Area Pct rank is for each account (within each business area) and year.

    I tried this measure, but it did not work:

    ASNeededPct Acct Rank:= 
    IF (
            HASONEVALUE (TestClients[AcctNo] ) && HASONEVALUE(TestValues[Year]),
    VAR MyCompany =
                    FIRSTNONBLANK ( TestClients[BusinessAreaID], 1 )
    RETURN
            RANKX (
                CALCULATETABLE (
                    'TestValues',
                    ALL (TestClients[AcctNo] ),
                    TestClients[AcctNo] <> "",
                    KEEPFILTERS ( 'TestValues'[Year] ),
                     TestClients[BusinessAreaID]= MyCompany
                ),
                [SprinklerNeededPct],
                ,
                ASC,
                Skip
            )
        )

    any guidance is appreciated.

    thank

    Scott




    • Edited by ScottCabral Thursday, January 10, 2019 3:58 AM
    Tuesday, January 8, 2019 2:26 AM

Answers