Home Forums Power Pivot Displaying top/bottom n entries as tables

Tagged: , ,

This topic contains 8 replies, has 2 voices, and was last updated by  tomallan 1 year, 11 months ago.

Viewing 9 posts - 1 through 9 (of 9 total)
  • Author
    Posts
  • #7544

    jw_dax
    Participant
    • Started: 1
    • Replies: 3
    • Total: 4

    Hi,

    I’m stuck on a problem and greatly appreciate any help you can provide.

    Attached is a workbook containing dummy projects’ details and scores. The ask is that when a user selects a region the dashboard should display the top and bottom n (defined by user) projects, along with the Project Code, Project Name and Project Manager Name. (That is, a table, not a pivot table.)

    I’ve included the traditional Excel approach, fixed to n=3, but am keen to do it in DAX. (Also, this doesn’t work if two projects have the same scores.)

    Many thanks,

    J

    Attachments:
    You must be logged in to view attached files.
    #7547

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2552
    • Total: 2559

    J,

    Just to make sure I understand, you are asking that the expected output is in “regular” Excel table format, not in pivot table format.

    Also, if 1st and 2nd are clearly the top 2, but 3rd place has a tie: how do you want to handle ties for third place (either include ties for 3rd place or create a tie breaker, so never more than 3 are displayed).

    Tom

    #7548

    jw_dax
    Participant
    • Started: 1
    • Replies: 3
    • Total: 4

    Hi Tom,

    Yes, as an Excel Table, please, not Pivot Table. Re ties – both ways would be great to know, thanks

    J

    #7549

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2552
    • Total: 2559

    What version of Excel do you have?

    #7550

    jw_dax
    Participant
    • Started: 1
    • Replies: 3
    • Total: 4

    Excel 2013. (Should have noted up front, as it’s an oft-asked question, based on your responses.)

    #7551

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2552
    • Total: 2559

    OK.

    Currently evening here, will work on it tomorrow morning.

    Tom

    #7569

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2552
    • Total: 2559

    To keep the workbook in xlsx format, I removed a “combobox” with regions that would refresh the workbook when the selected region would change.

    For this version, you manually enter the region in cell D3 and click the “Refresh All” button on the Data tab of the Excel Ribbon. Without the combo box it is up to the user to spell the region name correctly.

    The data in the Excel tables are driven by DAX queries, which you can read if you right click on a cell within either the table for the top 3 or the table for the bottom 3, then choose Table in the context menu and Edit DAX… in the submenu.

    The current version uses tie-breaking logic, but you could change it to also see results that respect ties, by using the formula for the [Low is Good] column for the Top 3 column and by using the formula for the [High is Good] for the Bottom 3 column.

    A pleasant surprise was that when respecting ties (instead of breaking them), Excel maintained the two row separation between the top table and the bottom table.

    Feel free to ask any questions.

    Attachments:
    You must be logged in to view attached files.
    #7575

    jw_dax
    Participant
    • Started: 1
    • Replies: 3
    • Total: 4

    Many thanks for your help, Tom.

    I like how you used the project number to force the tie-break.

    One thing (perhaps unique to Excel 2013) – the RANKX calculated columns returned errors, so I just replaced ASC and DESC with 1 and 0, respectively.

    #7578

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2552
    • Total: 2559

    J,

    You are welcome!

    Thanks for the feedback on ASC and DESC not used in Excel 2013. Currently using Excel 2016, and I just checked that 1 and 0 still work.

    For the first part of the tie breaker column, it is important that the score is formatted so that all values have the same number of digits before and after the decimal point. Also important that the vertical bar separate the score from the project number.

    Looking forward to working with you again in the PowerPivotPro forums,

    Tom

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic.