I have a spreadsheet that includes these columns: total AUM of firm X, % of AUM allocated by X to sector Y, % to sector Z, and so on (one column for each sector). Goal is to present in tabular form...

Posting as :
works at
You are currently posting as works at

Well, the short answer is you need to change the structure of your data: You don’t need the total AUM, but you need to convert the %s into their actual values. Your 3 columns should be:firm name, sector name, AUM. Use Google Refine to easily restructure your dataset if you need to alter rows and columns, use PowerBI or Tableau for visualizing


Use Power Query. You can pivot and calculate your percents and then you can unpivot to get back to columnar data all within PQ.




...how much AUM all the firms in the dataset allocate to each sector. Another goal is to also present how much AUM is allocated to each sector in a specific region. I’ve used a pivot table to create columns to calculate the AUM allocated to each sector by each firm, as well as to be able to filter by firm region. Is there a better way to do this?

Please help

Related Posts


Can you use the ey Christmas holiday break as a holiday week during the middle of maternity leave or does it count as part of the 16 weeks?


Can anyone please refer me for any openings in accounts payable profile ( PTP) , i have a experience of 2 years in Account payable domain.

please reach out to me if there are any referrals Or opportunities available.


Hi all, looking to career pivot from retail to talent acquisition/recruiting. Trying to make myself stand out to make up for my lack of experience. Some roles I am interested in prefer PHR or SHRM certifications. Would it be worth it to pursue a certification to land a position? Thank you


M33 Current Package is 31 LPA. i have received an offer from Singapore based MNC with 8k$ per month .
Could you please let me know is it worth to Move abroad for this package !


Hi fishes,
LTI is offering me 16lpa(base) +80 k (vp) +retiral benefits.
YOE- 5.2
Np left- 21 days
Tech stack- Ionic,Angular,Typescript
Is it underpaid? How much should I try to negotiate?


Has anyone noticed that you’re more depressed working fully remote? Having a quiet and non collaborative team is really making me think if I would prefer a hybrid position.


Hi there! Is it a good sign if a video interview runs 15 mins over? Was supposed to be 30 and it was 45


What is the general consulting or global consulting role in PWC @SDC do? And how is it in terms of growth perspective?


Hi Fishes,

Can anyone please suggest which company is better. Although I have joined Incedo a week back and getting a package of 18.5 LPA, plus I am holding an offer from HCL Technologies holding a package of 15 LPA.
I am quite confused with multiple factors, like job security and work life balance.


Looking for recommendations in Lisbon, Portugal. Will be in Portugal fur 2 weeks, curious to hear about your standout experiences.

Great restaurants, bars, clubs highly appreciated


Anyone here work at one of the Big 4 tax in Florida, preferably High Net Worth Individuals, Private Wealth or Private Client Services?

Would love to have a chat about how you find it etc


I was approached by a competitor for a job opening. Should I let my manager know that I'm getting job offers from other companies?


I've an experience of 20 months in Infosys . Last year during the appraisal my manager said that I am not eligible, as it's less than a year since I've joined.
How much of a hike in my salary could i expect in this year's appraisal?
Current CTC- 4 LPA


Anyone in here have experience with freelance writing? Any thoughts on this as a source of passive income? Sorry if this has been discussed before, new to this bowl.


Can I do PCS TAX as a finance major and pursuing my CFP? Or is a CPA 100% necessary? Any advice would help.


I’m getting really discouraged by the process of finding a daycare. Currently 32w pregnant and it’s crazy to me that literally no daycare I’ve spoken to can speak to availabilities in the Spring. It’s nothing but waitlists. Should I have started looking sooner? Starting to get worried that my maternity leave will and and we will still have zero plan on how to get childcare.


Hi Fishes, I am actively looking for opportunities within sales support functions and currently applying for vacancies for Sales Operations Analyst, I have 4 years of experience using Salesforce for reporting and dashboard needs and order processing. I wanted advice if there are any roles that I could target where I could utilise my salesforce experience? Any advice for upskilling for better opportunities? Thanks! 😊

Post Photo

Thoughts on financing a car vs pulling money out of brokerage acct to pay up front?

Additional Posts in Excel Genius

I'm stumped!
Coworker creates "Sheet A" with invoice numbers
I use Monarch to extract invoice #'s and amounts from another report.
I do a pivot table to compile amounts by invoice. I copy those results to "Sheet Z".
I then do an @vlookup on Sheet A referencing Sheet Z (just the invoice # and amount).
It isn't working unless I go to Sheet Z and type over the invoice # (same number)! All invoice #'s are absolute and in the same font & alignment. Workbook has 10 yrs data. Has worked in past!


Is there a way/shortcut, without using a mouse to drag cells down, to fill a range of data following a trend? I want a column with the first day of each month up to a certain date (row number). Thanks

Story time: I was on a trip about 15 years back, doing some Excel work on a ferry ride - your basic pseudo-database work that is so common. I started talking with the guy sitting next to me and after a while he commented on what I was doing, “You know, that’s really not what I designed that for…” (continued)


In VBA when sending an email. How do I change the reply to email?


Anyone know how to delete multiple custom cell styles at once without running a macro?

I’m trying to consolidated planning of FTEs from weeks to fiscal periods in a new table. If my column heading in the original table is 1, 2, 3 etc and my new table is Period 1, Period 2 etc
What is the most efficient formula to do this? Just doing =sum(Hours!E7:H7) for each period will take forever… thank you!!


Hi! Disclaimer: not an excel genius…

I’m trying to use the lookup function to grab data from a worksheet within the same workbook. I want the column to reference the cost code in one worksheet, find the cost code in the other and display the billed to date associated with it. The problem is that it repeats an amount randomly. There will be multiples then a blank cell in one that I know has a cost associated with it.


Any one good with Tableau I have a question


Has anybody used the COM library and python to build reports? I'm looking for somebody to chat with about my project.


Need to build a macro or python script to convert client spreadsheets to an import template. They are storing a process ID, metadata asset 1 (col B), metadata asset 1 attribute 1, asset 2 (col D), and asset 2 attribute on a single line. On our template, that should be 2 rows and then assign certain values based on the row being populated by col B/D. At a high level does this sound macro worthy or just start with python? I can give more details if needed.


Anyone have thoughts on best VBA/macro training out there? I find there are so many things I could automate and need a good place to learn the basics (particularly for excel).


How would I create subtotal rows in a table? A true table, I dont want to turn my table into a range because I need the other sorting features.


Flashfill not sequencing numbers when using the handle it’s just copying numbers....


Matching Data into Same Row

This may be simple or complicated:
I have a data set that is divided into 6 columns. I need to combine some of the rows together based off matching data.

What needs to happen is everyone with the same Bus Driver, needs to populate into the same row. Instead of the same Bus Driver being listed twice in the column.

For The names I plan on labeling them Person 1 | Person 2 | Person 3 | Person 4 | Person 5

I attached a smaller data set for an example

Post Photo

Hi, in my excel pivot table slicer I have my options and then (blanks) option , anyway to get rid of that?

I’m looking to make a supplier list where I can just filter on certain requirements to get a smaller list…currently creating as a pivot table but is there any other options that make sense?

Post Photo

Hello All,
Excel help please. I’m trying to combine 3 tabs into 1. So on tabs a,b,c I have wire instructions that go to different places, but they all have the same field names/format. However the number of rows for each tab will vary from day to day. I need to make a fourth tab that shows all the rows from a,b,c consecutively. Because the number of rows change, I can’t find the best way to collate all the rows into one table. I can use a macro or lookups or a source formula. Please advise!


Anyone have good tips for automating presentations/data visualization in PPT? These charts/graphs are pretty elementary so I’d rather steer clear of Power BI/Tableau if i can (no idea how to use and don’t have time to learn now) - just want to avoid pasting in dozens of charts into a PPT over and over each month.


Does anyone know if Excel is compatable with Adobe Sign/PDF? I'm trying to automate data entry from Adobe sign/PDF to an Excel sheet. Adobe


Hi Gang, I have voluminous pdf bank statements that I want to convert to excel with the formatting. What’s the easiest way out?


Need some excel help. I have a report that includes data for a bunch of accounts. However the report does not include a column for client name for each account. I'm waiting on our software engineer to pull the report but in the meantime I need to figure out a way to get the existing data to include the client name.

If I have a report with all accounts and client names and the second report with the data with the account numbers, is it possible to get excel to find the associated client name


New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here


Embed this post

Copy and paste embed code on your site


Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Send download link to your phone


Scan your QR code to download
Fishbowl app on your mobile

By continuing you agree to Terms of Use and Privacy Policy.

Messaging rates may apply

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or