LinkBack URL
About LinkBacks
I want to create a pivot table and do a count of the number of yes and no for each product group. My aim is then to calculate the percentage of the No for each product group based on the yes. Any help would be appreciated.
I want to create a pivot table and do a count of the number of yes and no for each product group. My aim is then to calculate the percentage of the No for each product group based on the yes. Any help would be appreciated.
The biggest help would be to have the yes and no in the same column if you wanted to use a pivot table
in e2 put
=IF([@No]="",[@Yes],[@No])
then pivot on this field, but the example contains no values of stock when cateogy is no
That's why I have used the Yes, No, where the stock is zero. But I need to count the zeros. I want then do a calculation to find the percentage the nos (or zero) are of the Yes where there are stock (if that makes sense).
I used Power Query to achieve the following results. Here is the Mcode.
Excel 2016 (Windows) 32 bit
Please Login or Register to view this content.
Product sum Yes sum No Pct No Product1 Product2 Product3 Product4 Product5 Product6 Product7 Product8 Product9
Sheet: Sheet1
Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps
M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Alan, thanks. I have not used Power Query before, but I'll follow your instructions.
Sorry Alan, how do I create a new PQ on the data in my original attached spreadsheet? I have never used PQ before.
Check out the links in my signature block -- that will be the best way to get started. Then if you are interested in learning more, get a copy of the book "M is for (Data) Monkey" by Ken Puls and Miguel Escobar. It will open lots of new worlds for you.
Alan
Thanks Alan, appreciate that. I can see how it would be useful. In the meantime, is it possible to do what I want in a simple Pivot Table with adding countif?
I can't. Maybe someone else is able. I will put in a call to others for help.
Thank you Alan. I appreciate your help.
If you change the YES and NO to just 1, you can then sum them as counters.
Rory
Rory, thanks for responding. I have attached revised workbook. I have added 1 for Yes and No columns as advised. I have added a pivot table, but am not getting what I expected. Any help would be appreciated.
Last edited by phynds; 06-17-2020 at 05:49 AM. Reason: omitted attachment
What do you expect? I think it would help if you added a mock up table of the results you actually want.
Sorry Rory, I thought it was giving me an incorrect number of yes, but it is correct. I think this will work ok. I'm going to test it on my original data now. Really appreciate your help.
No worries. Just post back if you have any problems with it.
Thanks to everyone for their help. All sorted. How or where do I mark this as solved?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks