Forum Discussion
Formulas and pivot table are driving me insane
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.
