February 7, 2017 at 5:28 am #7544
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.)
Attachments:You must be logged in to view attached files.February 7, 2017 at 5:51 am #7547
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).
TomFebruary 7, 2017 at 5:59 am #7548
Yes, as an Excel Table, please, not Pivot Table. Re ties – both ways would be great to know, thanks
JFebruary 7, 2017 at 6:23 am #7549
What version of Excel do you have?February 7, 2017 at 6:25 am #7550
Excel 2013. (Should have noted up front, as it’s an oft-asked question, based on your responses.)February 7, 2017 at 6:29 am #7551
Currently evening here, will work on it tomorrow morning.
TomFebruary 7, 2017 at 8:11 pm #7569
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.February 7, 2017 at 10:13 pm #7575
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.February 7, 2017 at 10:40 pm #7578
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,
You must be logged in to reply to this topic.