• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count Distinct in Pivot Vs Calculated Field in Pivot data model - the fine missing balance act !

Folks,
Here's a poser on Excel Pivots !
'Count Distinct' as a Value field setting is only available in Pivot, if the Pivot is created as a Data Model. However, In Data Model type of Pivot, a useful feature of 'Calculated fields' is suppressed :( ! Is there a way to achieve either (a) count distinct in regular pivot table or (b) calculated field in data model pivot ?

For now, i create two pivot charts - one on data model and other regular, and then copy paste the result on the other - crude, but works for now. Thoughts ?!

PS: I found a similar post here - though it's silent on this specifically (I hope it is not 'Not possible') https://chandoo.org/wp/distinct-count-pivot-tables/

Thanks !

Regards,
Sudhir
 
Last edited:

GraH - Guido

Well-Known Member
However, In Data Model type of Pivot, a useful feature of 'Calculated fields' is suppressed :( !
'Auwch'. With data model pivot (PowerPivot) you have DAX (data analysis expressions) at your disposal to create measures. Beats calculated fields big time.
Perhaps you can upload a sample file containing an example of the results you are after. Those can be created manually.
 
... btw, so very silly of me. Another simple workaround is to add a calculated column in data manually and then pull the sum in Pivot. Will wait for your revert though, if DAX can teach me a new trick (will check out on my own as well...) thanks
 

GraH - Guido

Well-Known Member
Sure you can make a calculated column, but often it is not required, and it will bloat your model as more memory is used. Measures will demand more from CPU.
I recommend if you want to learn about DAX seriously to read some books. On release of PowerPivot it was called the Excel revolution.
61830
 

Attachments

Hey Guido,
Thanks for the note - learnt a cool new feature thanks to you ! Gonna continue more research on this ... Cheers !
Sudhir
 
Top