• 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:
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.
 
Hey Guido,

Thanks for the revert. Here's a sample I created. Hoping to learn that trick on DAX from you. Thanks !
Sudhir
 

Attachments

  • Sample data.xlsx
    176.7 KB · Views: 6
... 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
 
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

  • Copy of Sample data.xlsx
    202.5 KB · Views: 4
Hey Guido,
Thanks for the note - learnt a cool new feature thanks to you ! Gonna continue more research on this ... Cheers !
Sudhir
 
Back
Top