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 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('tab..'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

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.

Inquiry1.PNG

[Bo_Ry]

Append Table1-3 and re-create the relationship

Model.png

[happydays886]

Append Table1-3 and re-create the relationship

Attachment 726407

This is amazing - I am unfamiliar with appending tables.

I figured it out - thank you!
- select all tables and hover "..." to append; append >3

Wow. this is amazing!

Thank you!

[happydays886]

Append Table1-3 and re-create the relationship

Attachment 726407

When I recreate in the test data set - no issues.

When I apply against the primary data set, the sum formula on the appended table values pulls an error.

Resolved - the data I had was bad. After cleaning it up and building a bigger mapping set, it worked. Whole thing works as intended and is absolutely gorgeous.

Thank you!