Forum Discussion
Formulas and pivot table are driving me insane
I am attempting to connect data from two workbooks. I figured out how to import my agent's from the company workbook data to my workbook and update real-time. Now I need to figure out how to have the data automatically transition to a sheet with my specific team of agents. I need the sheet to match the names from the master list to the specific agents on my team. Then populate in a cell how many times the agent's score was less than 6, A cell for agents with scores less than 8, and a sell with scores more than 9. Next to their names. IF anyone can help I have attached examples.
From the pictures, one picture is the master list this list is in my workbook That I copied over from the company workbook, The master list contains all agent's scores, I need excel to extract data from my specific agents on my team from the master list. And count how many times this agent received a score that met the criteria. I need the number of detractors, passive, and promoters scores. I will also need this to be able to update automatically with the changes from the master list that is connected to from company workbook the agent can show on the master list multiple times. I have blocked out all sensitive data to assist with reviewing the layout. Thank you for your help!
- please replace the pictures with the rows and columns included if you're going to need help with formulas
I restrict myself to defined names and dynamic arrays so your implementation may be different.
= COUNTIFS( Full_Name, Agent_Name, NPS_score, {"<8","=8",">8"})If with PivotTable you need to add data to data model creating it and add 3 DAX measures like
Defactor:=CALCULATE(COUNTROWS(MasterList), MasterList[NPS Score] < 😎Two others are similar.
That was a bit of a battle for me. I have never had access to corporate data for analysis so the pivot table is a rarely visited area (if I can't type the entire dataset in less than 10 minutes, I lose interest).
I took the RANDARRAY and used formulas to produce a separate Table that I could upload via PQ. That generated a data model was always a refresh out of sync with the source data so I output a further static copy to the worksheet. Then I moved to finding out how to define measures and avoid overwriting those already defined. I have still got to get the column headers in order (I suspect that requires a custom list but who knows?). A few lessons learnt along the way! Thank you.
Note: I remember reading an article that confidently asserted that between 95%-99% of spreadsheets were database tables and was struck by the extent to which it is possible to see the world from different perspectives. When I looked at engineering companies, I got a far smaller proportion (less than a quarter).
I only jumped in with measures for the case if the solution with data model is already in place and we only need to expand it with few other measures. If Power Query already is here to pickup external data, most probably it's better to do everything with it. If nothing of above formulas you suggested is optimal variant. Everything depends on context.
