Hi All,
Is there any method or workaround to count distinct value & SUM in the same pivot table?.
If we tick on add data model in pivot table, we may not be able to calculate SUM
Untitled.jpg
Hi All,
Is there any method or workaround to count distinct value & SUM in the same pivot table?.
If we tick on add data model in pivot table, we may not be able to calculate SUM
Untitled.jpg
1. Thank those who have helped you by clicking the Star * below the post.
2. Please mark your post [SOLVED] if it has been answered satisfactorily.
Sincerely,
Farid
Why would you want to sum a text field??
Rory
Hi Rory,
I need to count unique value of borrower group & borrower name, sum of outstanding value, total approved limit and so forth. I know excel formula can solve it but it would be nice if both requirement can be done in the same pivot table.
I have many more columns and thus it would be easy to use pivot table since we are able to drag in/out during presentation.
Ok, if I do not add data model to pivot table, Is there any workaround to count unique value? Perferably without using formula because time consuming during presentation.
Last edited by Faridwahidi; 03-11-2019 at 08:39 PM.
My point is that you can add the data to the model and then sum and count distinct. You just can't sum a text field.
Hi rory,
When data added to the model, SUM will not working. I have tried many times. If you can do it, I would much appreciate if you can attach sample file.
The data model would be entirely pointless if you couldn't sum anything.
Please post your sample workbook and explain what you are trying to sum that you can't.
Hi rory,
Sorry, I was overlooked both columns to count sum are text format. After change to number format, it worked.
Anyway, thanks for responding.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks