How can I modify this pivot table to show the desired data?

Lopez, Ahiezer 236

I am creating a pivot table where the row is the reason code, the column the month/year, and the values Distinct Count of Production Number. There are moments where a production number is found in reason code 4 and 22 or in 5 and 26. Regardless, how can I make the pivot table only count production numbers that are found in one of the 6 different reason code groups, ignore any more that are in other groups. Hope this makes sense.
![275780-image.png][1]
[1]: /api/attachments/275780-image.png?platform=QnA

2 answers

Emi Zhang-MSFT 25,546 Reputation points Microsoft Vendor

2023-01-04T08:07:19.637+00:00

Hi @Lopez, Ahiezer ,
It's not very clearly about your requirement, what's the meaning of "production number is found in reason code 4 and 22 or in 5 and 26"? And what is the "code groups"?
I suggest you check if the slicer feature can help you, it's the way to filter the data in PivotTable:
https://support.microsoft.com/en-us/office/use-slicers-to-filter-data-249f966b-a9d5-4b0f-b31a-12651785d29d

If the groups related to the data source table, you should provide the data source here. And you can also provide the result table here, so that I can get more accurate solutions to this problem. I’m glad to help and follow up your reply.

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
Lopez, Ahiezer 236 Reputation points

2023-01-04T15:56:32.1+00:00

Hi @Emi Zhang-MSFT ,

Thanks for responding.
A particular production number will be reported with more than one reason codes. For example production number 123456 will be reported with reason code 4, 5, 20. However, with the pivot table I only want to count it once despite being recorded 3 times. The production number may sometimes only be reported with one reason code or other times with more than one. Despite all that, I would like for it to be counted only once.
Hope this makes a little bit more sense.

Emi Zhang-MSFT 25,546 Reputation points Microsoft Vendor

2023-01-05T08:58:25.283+00:00

Hi @Lopez, Ahiezer ,
"For example production number 123456 will be reported with reason code 4, 5, 20. However, with the pivot table I only want to count it once despite being recorded 3 times." This means the counting result for all reason code in each reason code rows?

Emi Zhang-MSFT 25,546 Reputation points Microsoft Vendor

2023-01-05T09:06:40.137+00:00

Could you provide a simple sample data source table here? So that I can test the PivotTable and if you use any measure you can also provide here.

Lopez, Ahiezer 236 Reputation points

2023-01-05T21:49:12.73+00:00

You should have access to the link below that contains an excel file with a sample data.

https://www.dropbox.com/scl/fi/zvkg772x8mw9tewrkafzp/Sample-Data.xlsx?dl=0&rlkey=jd019w3mr4th7vew8bnjrq6zz
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Herbert Seidenberg 1,191 Reputation points

2023-01-08T19:55:37.877+00:00

Excel 365 Pro Plus with Power Pivot and Power Query.
Transform with PQ.
https://www.mediafire.com/file/0ldht06wyoz5opf/01_05_23a.xlsx/file
https://www.mediafire.com/file/9tcza53zx1kjxm2/01_05_23a.pdf/file
Please sign in to rate this answer.
Lopez, Ahiezer 236 Reputation points

2023-01-09T23:32:29.147+00:00

Thank you for this.

Could you provide a bit of explanation for what it is that you did?

Herbert Seidenberg 1,191 Reputation points

2023-01-10T18:30:47.037+00:00

Just a fancy way of removing product duplicates. Did you try it and get the desired results?

Lopez, Ahiezer 236 Reputation points

2023-01-17T22:51:11.34+00:00

@Herbert Seidenberg

It seems to be working however my pivot table after implementing your power queries suggestions, is not the same as yours.

Herbert Seidenberg 1,191 Reputation points

2023-01-19T16:28:55.77+00:00

Your 2nd database seems to be larger than your 1st.

You did not use Power Pivot.

You did not show how you tweaked my PQ to accommodate your data and layout.

You did not show which data point(s) do not meet your specifications.

Your specifications are vague. Does "other groups" include months?

No shared file provided with actual, calculated and desired results.

Lopez, Ahiezer 236 Reputation points

2023-01-24T23:13:29.0266667+00:00

I did use a larger dataset. The sample data I shared was a smaller dataset.

I did not use a power pivot. Didnt realize I had to use the power pivot instead of a regular pivot.

Using my larger dataset, I applied the following steps Since my larger dataset contains dates from 2019, 2020, and 2021. I attempted to keep the same date format I had from the start so that when I created the pivot table, the columns would show the "Month-Year".

Herbert Seidenberg 1,191 Reputation points

2023-01-25T14:56:06.67+00:00

Pass

Unresponsive
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.