Power BI User Group of Philadelphia

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Rank Issue

  • 1.  Rank Issue

    Top Contributor
    Posted Apr 18, 2019 04:53 PM
    ​It might be that I have been staring at this two long, but my rank doesn't appear to be working.  I whited out the account names and numbers, but you can assume that they are there and unique.  In my measure, I am ranking account number based on total expense, however, as you see the rank is not correct.  I should have 1 through infinity descending based on the Total Expense (this is the same as SUM('Actuals'[Amount])).  Any ideas why this is not working.  Also, I am using RANKX in a calculated column.  The 30% parameter is not impacting this either, does nothing at moment.


    Rank Issue by Total Exp

    ------------------------------
    Brian Connelly
    Sr. Associate, RSM US
    Northeast Business Intelligence Practice
    ------------------------------


  • 2.  RE: Rank Issue

    Bronze Contributor
    Posted Apr 19, 2019 08:40 AM
    Hello Brian,
    Just curious, is there a reason for using the CALCULATE around the RANKX? Also did you try doing the ALL on the entire table and not just 'Account Number'?

    ------------------------------
    Best Regards,
    Andrew Foulk
    ------------------------------



  • 3.  RE: Rank Issue

    Bronze Contributor
    Posted Apr 19, 2019 09:19 AM
    Brian,

    I agree with Andrew and I'm also curious on the use of Calculate, but anyway assuming the end goal is the connect the RANKX measure to the Parameter here is the measure that I have used in my models that works.

    Extended Weight Rank Dynamic =
    VAR TopRank =
    RANKX(ALL(Customers[Group Name]), [Lbs. CY],,DESC)
    VAR SelectedTopN =
    IF (
    HASONEVALUE ( TopNN[TopNN] ),
    VALUES ( TopNN[TopNN] ),
    MAXX ( VALUES ( Customers[Group Name] ), TopRank )
    )
    RETURN
    IF (TopRank <= SelectedTopN,[Lbs. CY])

    and here is the Lbs. CY measure

    Lbs. CY = SUM(Sales[ExtendedWeight])

    ------------------------------
    Barry Crowell
    BI/EDW Solution Architect
    KTL Solutions, Inc
    Frederick MD
    301-360-0001
    ------------------------------



  • 4.  RE: Rank Issue

    Posted Apr 19, 2019 09:21 AM
    ​I did this once, and took a long time to get it working, but this is what works for me.   I updated the variables to your variables, perhaps this will work for you.

    Account Number Rank = IF(
        HASONEVALUE('Actuals'[Account Number]),
             RANKX(ALL('Actuals'[Account Number]), 'Actuals'[Amount],,DESC)
        )


    Greg

    ------------------------------
    Greg Hackenberger
    Programmer/Analyst
    Glatfelter Insurance Group
    York PA
    ------------------------------



  • 5.  RE: Rank Issue

    Bronze Contributor
    Posted Apr 19, 2019 12:36 PM
    Brian,

    I maybe in how the data has been modeled.  Here is a link to my sample PBIX file.  The RankX measures work in the file.

    ------------------------------
    Barry Crowell
    BI/EDW Solution Architect
    KTL Solutions, Inc
    Frederick MD
    301-360-0001
    ------------------------------



  • 6.  RE: Rank Issue

    Top Contributor
    Posted Apr 19, 2019 09:44 AM
    ​Andrew,
    It is there as left over trial and error processing before I ask you amazing people.  I actually started with RANKX(ALL(Actuals[Account Number]),[Total Expense],,DESC) which wasn't giving me the correct ranks (same as in Image) then I changed to RANKX(ALL(Actuals[Account Number]),SUM('Actuals'[Amount]),,DESC) without a calculated() which then I wrapped in the CALCULATE as you see.

    ------------------------------
    Brian Connelly
    Sr. Associate, RSM US
    Northeast Business Intelligence Practice
    ------------------------------



  • 7.  RE: Rank Issue

    Top Contributor
    Posted Apr 19, 2019 09:45 AM
    Hi Barry,
    See reply to Andrew.​

    The problem I am having is with the RANKX(ALL(Customers[Group Name]), [Lbs. CY],,DESC).  This ranking is not calculating correctly.

    Brian

    ------------------------------
    Brian Connelly
    Sr. Associate, RSM US
    Northeast Business Intelligence Practice
    ------------------------------



  • 8.  RE: Rank Issue

    Top Contributor
    Posted Apr 19, 2019 09:51 AM
    Thanks Greg, odd.  This returns no values.​

    ------------------------------
    Brian Connelly
    Sr. Associate, RSM US
    Northeast Business Intelligence Practice
    ------------------------------



  • 9.  RE: Rank Issue

    Posted Apr 19, 2019 11:22 AM
    ​Strange that you get nothing.  I've played around with my table, and the presence of two text fields seems to through off my rank, but I still get some value and not nothing.  But I wonder if having both Account name and Account Number in the table throws off the Rankx?  My Model has what would be your Account Number as the first table value and Rank second and the Amount as third and works for my report.  Curious if a simple 3 field table works?

    Greg

    ------------------------------
    Greg Hackenberger
    Programmer/Analyst
    Glatfelter Insurance Group
    York PA
    ------------------------------



  • 10.  RE: Rank Issue

    Bronze Contributor
    Posted Apr 19, 2019 11:44 AM
    Greg has a point, it could be the multiple fields.

    Try using this measure (with your fields replacing the example's)
    RANKX on 2 categories

    ------------------------------
    Best Regards,
    Andrew Foulk
    ------------------------------



  • 11.  RE: Rank Issue

    Bronze Contributor
    Posted May 02, 2019 03:26 PM
    I'll often eschew the ranking functions and use counts.

    Count of Accounts with Lower Expenses =
    IF (
        HASONEVALUE ( 'Account'[Account Number] ), //assuming you have an account dim
        VAR CurrentAccountExpense = [Total Expense]
        RETURN
            COUNTROWS (
                FILTER (
                    ALL ( 'Account'[Account Number] ),
                    [Total Expense] <= CurrentAccountExpense
                )
             )
    )


    This will return a result only if you're in the context of a single account number, as it doesn't make sense to assign a rank in the context of multiple accounts. That's the IF

    We capture the total expense in current filter context into the variable CurrentAccountExpense.

    Then we iterate a virtual table consisting of all unique account numbers (the ALL). For each row in that table, we calculate total expense of that account and compare it to the value we've captured in CurrentAccountExpense. Thus the FILTER returns a 1-column table of accounts with lower or equal expenses compared to the account in filter context. Then we just count the rows of that table.

    ------------------------------
    Greg Baldini
    Data guy
    ------------------------------



  • 12.  RE: Rank Issue

    Bronze Contributor
    Posted May 03, 2019 08:49 AM
    FYI Greg,
    I am deeming you as 'The Great Greg Baldini'. That should be your presenter name. haha

    ------------------------------
    Best Regards,
    Andrew Foulk
    ------------------------------



  • 13.  RE: Rank Issue

    Top Contributor
    Posted May 06, 2019 11:11 PM
    I'm not sure what my issue was, I gave up.  I just added a top 10 filter on the visual.  Normally, the ranks work so I have no clue what was happening.  I appreciate are the help.

    ------------------------------
    Brian Connelly
    Sr. Associate, RSM US
    Northeast Business Intelligence Practice
    ------------------------------