Power Pivot: Measure to sum unpivoted values?

Happydays886

New Member
Joined
Mar 31, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello.

I am trying to build a model that will summarize payroll data efficiently - the data is not uniform and would require a lot of manual manipulation. This is why I turned to PowerQuery/Pivot to help organize the data and push out values by their intended bucket (screen shot).

There attached file provides a fairly good representation of the problem:
- Multiple data sources
- Columns that need to be summed
--> before unpivoting, sum(...) pulls correct amounts with each column

However, I need to map columns to a specific attribute (account number) and present as in the picture below. So, I unpivot "earnings, tax, bonus". I map 'attribute' in each data set to 'attribute' in an account file.
- Columns that need to be mapped to a specific account (why they are unpivoted)
- display output/result by:
--> account
--> month

All formulas yield errors - on review, I see this could be on account of the mapping: when unpivoting the data, the unique identifiers blew-up in the source data?

All help is appreciated. I hope this is just a formula to fix.
 

Attachments

  • Inquiry1.PNG
    Inquiry1.PNG
    8.5 KB · Views: 2

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Happydays886

New Member
Joined
Mar 31, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I'm not quite sure how to edit an original post.

I posted in a different forum that allowed full file to be loaded. Here are pictures of the data to illustrate the problem better:

Before unpivoting the data columns (earnings, tax, bonus) - sum('table..'earnings), etc... works
- after unpivoting, all formulas break because those columns are removed and replaced with "values" - a new column is created "attribute" that is the header for each column pre-unpivot

When I attempt to "sum(values)", using any array of "calculate(sum.., sumx,... " all efforts result in an error.

On review, I believe it is because the unique identifier is now duplicated within each data set.

Help is appreciated
 

Attachments

  • Datai2.PNG
    Datai2.PNG
    39.9 KB · Views: 3
  • Mapping.PNG
    Mapping.PNG
    22.4 KB · Views: 3

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
Thanks for mentioning that you have posted this on another site & got an answer.
But can you please post a link to the other site, as per board rules. That way the answer may help somebody else in the future.
Thanks

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,673
Messages
5,637,718
Members
416,981
Latest member
PLonchar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top