Hello,
I cannot get my pivot table to count unique instances of a PO number. Instead it counts how many times a PO appears.
The attachment shows a sample of raw data and the result I want along with the result I am getting.
I have tried using the data model option in excel 13 but when I do that it will not SUM my spend, an error message tells me it is in text.
Can you help?
Last edited by bigsi1984; 11-02-2016 at 05:40 AM. Reason: To add attachment
That looks a bit muddled. Here you go
Attachment 487299
You've just discovered one of the many quirks about this forum...
Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Thanks,
I have sorted the attachment now.
In B15, this array formula, copied down:
=SUM(IF(FREQUENCY(IF($D$4:$D$10=A15,$A$4:$A$10),IF($D$4:$D$10=A15,$A$4:$A$10))>0,1))
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
Hi,
Using Power Pivot in Excel 2010 works as expected- I have attached your workbook with the pivot added. You may be able to simply upgrade the model to see it in 2013.
Don
Please remember to mark your thread 'Solved' when appropriate.
I don't appear to have power pivot. It is not listed in my COM add-ins.
Your sheet works as I want it to but when I try it on my actual data set I still get the an error trying to SUM anything in the values box. It says it is not a supported calculations for text data types
Are you sure that your cost data is not actually stored as text rather than a true number value?
It's currently stored as accounting but I have tried copying the whole set into a new workbook and paste special values then reformatted the columns to number and I still get the error message.
If you alter the number format of the cells, do the displayed values actually change appearance?
Yes. So they are currently account so showing £xx.xx and number will show them as xx.xx
Does it perchance make a difference if the numbers are formatted in a non-accounting format before they are loaded into the data model?
I have been playing around with it and I figured out the problem. The spend cell uses and IF statement to work out the cost so it's always seen as text. I have fixed this to be a standard formula and everything works fine. The problem now is that I cannot group the months together in the data model! I think without power pivot this is not possible.
You can always add month and year columns to the source data.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks