Power Pivot: Measure to sum unpivoted values?

[Happydays886]

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.

 

[Happydays886]

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

 

[Happydays886]

solution = append tables 1-3 in a different forum.

 

[Fluff]

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.