Finding values > £150,000 from the pivot table and then show them on chart

[Jigneshbharati]

Good afternoon,

I hope you are well! I have attached a small file for my query but actual data have different column names and more no of columns and values.
I have a pivot table that has four columns for four different organizations (Com A, B, C, ...) and in actual data, there are 98 rows (category of products). I want to review categories which is more than £150,000 across those 4 organizations. I hope it makes sense. Is there a way that I can do a nice pivot chart for all those 4 organizations, comparing them with the condition: > £150,000.

Thank you for your help and time.

Kind Regards,
Jignesh

[josephteh]

Add a calculated field to total up the category over the 4 organizations, then filter the total.

[Jigneshbharati]

Thank you so much! I am so sorry that I might not have explained my question properly.
Basically, We want to review all the category A,B,C,... I where their values is > 150,000 ( it's not the total or subtotal). For example it should show or highlight the values from Com A column as they are > 150,000: 155000, 260000, 550000, 250000. Similarly for Com B it should filer the values: 300000, 155000, 120000, 180000,159000. For Com C :184000,250000,260000,340000

So we want to use this filer value (£150,000, any category which costs > or = £150,000). Basically If I want to do analysis on Com A, I want to find all the categories which cost >= £150,000 for this and similarly for Com B and Com C.
I hope it makes sense.

Thank you so much for your time and help!

[Jigneshbharati]

Could someone please help with this? Thank you in advance!
Please refer the above (the post or reply by me at 9:56 A.M) for more information.

[josephteh]

How would you want your result to look like? Use conditional formatting to highlight those values > 150,000? Please mock up your result.

[Jigneshbharati]

Thank you. I have highlighted the cells >= 150,000 for each COMP A, B, C. I don't want the "Sum of Total" column after the COMP C.

Is there a way once you have a pivot table, you can insert a slicer to select the Organisation COMP A, COMP B or COMP C and then it
should only show those highlighted values for that column? Thank you in advance.

For example We are reviewing COMP A and one of the categories called B has 155,000 but is there a way to show these comparison values from

COMP B 5,000 (this means COMP B has done well for the category B but COMP C has not as they have spent more than £150,000 (184,000)

Thank you so much for you help. I am so sorry , I am learning Excel and only have basic knowledge.

[josephteh]

Can other forum members please help OP? Thank you.

[AliGW]

Is this what you want?

Excel 365 (Windows) 32 bit

	F	G	H	I
13	Category	Com A	Com B	Com C
14	A		300000
15	B	155000		184000
16	C		155000	250000
17	D	260000
18	E	550000
19	G		180000	260000
20	H	250000
21	I		159000	340000

Sheet: Sheet1

If so, it's easy in PowerQuery.

M Code:

Please Login or Register  to view this content.

[Jigneshbharati]

Yes. Exactly.
My Excel knowledge is very basic as my main job is in the clinical pharmacy team for the NHS. How do I go about this. Could you please guide me through? In my original Pivot table, there are 98 rows (corresponding to the category in my example pivot) and there are 4 CCGs (Clinical commissioning groups) in the columns ( which corresponds to Comp A, b, and C). What we want is to review all the categories ( The real data represent dressings and appliances ) which cost >150,000 in each CCGs ( COMP A, B, C in our example) and also compare them within 4 CCGs. I hope it makes sense. I really appreciate your help and time.

My question relates to the pivot table and a corresponding chart ( The above data ( values > £150,000 for each category) is after creating a pivot table)

Have a nice day.

[AliGW]

My point is that you can use PQ to do what you want INSTEAD of the pivot table.

I have updated the query to load to a pivot table for you, and from this you can create a chart or graph, if you wish.

Excel 2016 (Windows) 32 bit

	F	G	H	I
2	Sum of Value	Column Labels
3	Row Labels	Com A	Com B	Com C
4	A		300000
5	B	155000		184000
6	C		155000	250000
7	D	260000
8	E	550000
9	G		180000	260000
10	H	250000
11	I		159000	340000
12	Grand Total	1215000	794000	1034000

Sheet: Sheet1

[AliGW]

And with a chart added.

[Jigneshbharati]

Thank you so much.

In the original data, there are 98 to 150 categories (same as in our example A, B, C, D,...I and there are four CCGs we are looking at similar to Comp A, B, C. Which chart should I use and how to show my requirements ( all those categories (dressings or appliances ) whose value > 150,000 for each CCGs and also show compare them within 4 CCGs?

My original data have different column names and is comparatively bigger than our fictitious example. How do I apply your code? what do I need to do?
Thank you so much and it does make sense now.
Have a nice day.

[AliGW]

The chart you use is up to you. You can change it using the built-in options.

If the sample data is not fully representative, then you need to provide sample data that is. We can only provide you solutions for the data you provide.

See post #11.

[josephteh]

Attached file using Power Query and PivotTable, with conditional formatting to grey out values less than or equal to 150,000.

Finding values more than £150,000 from the pivot table and then show them on chart.png

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[AliGW]

Which solution was the one you chose, out of interest?

Thanks for the rep.

[Jigneshbharati]

Thank you so much for your time and help!

[Jigneshbharati]

Which solution was the one you chose, out of interest?

Thanks for the rep.

I like the power query idea as I have never learned yet.

[AliGW]

Good man! You won't regret it.

[Jigneshbharati]

The actual table has 98 categories and 4 columns for 4 organizations. How do I apply the power query to my original data as per the above example? Thank you in advance. I am so sorry that I haven't used this feature before.

[josephteh]

How many categories is not an issue. The column names may be an issue. In my PQ, only "Category" column name is hardcoded, the rest are not and you can safely add more organizations.

Come back if you have problems adapting to your actual data. All we need is your actual column names.

[Jigneshbharati]

Thank you so much. Could you please tell me what steps I need to follow from your example?

[AliGW]

Yes, I will do - but I am busy at the moment. I will write instructions later or tomorrow morning.

[Jigneshbharati]

Thank you so much for all your help and time! Much appreciated.

[Jigneshbharati]

https://docs.microsoft.com/en-us/pow...ry/share-query
I have used this link and tried to copy the M code but no luck. I have managed to copy the M code but When I open my work book and go to "data" then "queries and connection" and I can't do anything- nothing works.
I can't paste it or righ-click it.

you can see the photo "queries and connections". I can't right-click anything or add or paste anything. I am using Microsoft 365.

Thank you for your help.

[AliGW]

Just wait, please - I will explain it to you as soon as I have time.

Be patient!

[Jigneshbharati]

Sorry! Thank you....

[AliGW]

Here we go:

1. Select any of the cells in the source data table (A2 to D11 in the sample workbook) and click on Data | From Sheet.
2. In the PQ editor window, select the first column (Category) so that it's highlighted in green (if it isn't already so) and go to Transform | Unpivot Columns ... (click the little arrow to drop down) | Unpivot Other Columns.
3. Select the Value column and click on the Filter button at the top of it (arrowhead) - choose Number Filters > Greater Than ... and type 150000 in the top right box > OK.
4. Select the Attribute column and click on the Filter button at the top of it (arrowhead) - choose Sort Ascending > OK.
5. Go to Home | Close & Load ... (click the little arrow to drop down) | Close & Load To ... > select PivotTable Report.
6. Set up the pivot table with Category as rows, Attribute as columns and Value as values.
7. Go to the Design Ribbon | Grand Totals | On for columns only.
8. With a cell in the pivot table selected, go to Insert | Pivot Chart and choose the layout you want.

That's it.

[Jigneshbharati]

Thank you so much for your help! I will give it go and see if I can follow the steps without any more help.

[AliGW]

You're welcome! Just post back here if you encounter any issues, but remember if you're struggling with it on a different dataset, then you'd need to provide a new, representative data sample here.

[Jigneshbharati]

Thank you so much. It did work. Out of interest, I still want to know, How do you copy and paste someone's M code given in the reply by an expert so that I can replicate the answer? This little trick will be very handy for my learning.
Have a nice day!

[AliGW]

If you want to copy and paste M Code, you need to make sure first that:

1. You source data is already in the workbook you are pasting it to (if the query reads from a workbook table, as this one does), and ...
2. Your source data table name is the same as the one referenced in the query (usually, by default, this will be Table1, but you can check in the Name Manager on the Formulas ribbon).

As long as your source table name matches the query code and the column headers are the same, you're good to go.

1. In the workbook containing the source data, go to Data | Get Data > From Other Sources > Blank Query.
2. In the PQ window, go to Home | Advanced Editor and paste the M code you have in the place of what's there (do not leave any of the default code as it won't work).

You should now have everything you need in the query ready to Load To ... > PivotTable Report as before.

[Jigneshbharati]

Thank you!

[AliGW]

My pleasure. PowerQuery is worth learning, for sure. Have fun!