Using a PivotTable to Sum and Rank Data (OLAP)

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
I'm not sure whether I understand you correctly. Especially I don't get your data flow here.

But a clean table would have a structure of Record ID, Partner, AAA, BBB, .. etc.

So the question is: Where does the data come from? It would be easier for example to unpivot the data in T-SQL.

Otherwise use one of the free unpivot tools in the wild or write your own VBA function doing that.
ProfessorJimJamMicrosoft Excel ExpertCommented:

please find attached.

first in the sheet solution, column A i have put a formula to extract unique values from that Named Range "tbl_Text" which is a created named range in all partners data. then in column B i used countIF to count how many times they appears there. then i created the column A and B as Table2 and then i used the pivottable from table2 and then in the pivot table i used the value filter "Top Ten"
see the screenshot on how to filter pivot table for top ten.

-PolakAuthor Commented:
@ste5an it's repeating data submitted into a flat database (SharePoint list).... I'm fine with VBA to restructure the data table, send it into PowerPivot as a related table, and then use some combination of CALCULATE, RANKX, and calculated fields to get to my solution. It's the data structure writing the function that's holding me back.

@ProfessorJimJam you continue to impress me with you mastery of excel functions/formulas. However, I'm unsure of how to implement your solution using an OLAP Pivot table... ? Did you see in my original question where I mentioned that whatever the solution is it MUST utilize an OLAP PivotTable? This is because my current slicers are OLAP and whose PowerPivot Table can only be related back to another PowerPivot Table. (I do not wish to create a synchronization between Non-OLAP slicers and OLAP ones, boggs down the workbook too much).
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ProfessorJimJamMicrosoft Excel ExpertCommented:

can you try now with the attached version, this one does not use any pivot table and basically the top ten is extracted with formula.
-PolakAuthor Commented:
Hi JimJam, I know that we can do it this way, but for my purposes I need the Top 10 to be dynamic with some slicers I have made on an OLAP data source. Maybe if I illustrate it will be easier to understand.... please see the attachment.

If you could somehow retain record ID in your Table2, it might be possible to link that to the PowerPivot data model and use a pivot table from there?
-PolakAuthor Commented:
Disregard my Last post if you read it, I'm closer to a solution please see the attached.... This version uses an OLAP slicer but as you can see the value for AAA is much too high....

PS that Array that you wrote for finding the Mission Partner is awesome, had no idea you could do that.
ProfessorJimJamMicrosoft Excel ExpertCommented:

i saw your latest attachment and you were almost close, the only problem you had was that the last column was triple counting and miscounting the numbers.

so i have fixed it and now it works.  please see attached.

if you want to know how did i do it, then here are the steps.

A) you must have Microsoft Power Query
B) select the whole table and copy and then paste as values, then
C) Delete Column CJ Mission total
D) launch Power query and from there select from Table then once it is loaded then select whole columns from Au to CI and then on the Transform Tab as shown in the screnshot below, click Unpivot columns
E) you will see that all of the columns are unpivoted and are transformed in a different way easy and ready for pivot table.  then from there you creare your pivot table exactly as i did it.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
-PolakAuthor Commented:
Thanks, up until today I had no idea that Power Query was a free add-in. Thanks Jim!
ProfessorJimJamMicrosoft Excel ExpertCommented:
you are welcome Polak.  I am glad, i was able to help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.