Hi Experts, I've attached a sample workbook.
I am attempting to clean some concatenated data AND then rank the prevalence of how often a Partner appears across all records. Now, I realize that this would be easy if I wanted to just build a summary table with all of the Partners on the solution worksheet and input a simple CountIf into the summary table.
However, I need for this to work inside of a pivot table because I'm slicing the records via several other criteria not in this sample workbook. I've played around with Calculated Fields and DAX expressions, but I can't really get them to work with how the data is structured. As such, if you want to clean the Data in the CleaningTable and update the PowerPivot Data model, by all means go right ahead. However, whatever solution you come up with MUST utilize an OLAP PivotTable.
My goal is to rank the how often a "Partner" appears in the data and display the value on a table for the "top 10 Partners". For example, Partner "222" will have a rank of 1 when viewing all records.