Creating Secondary Table that will Add Multiple Rows together for an Attribute

[kressb]

Hello, I need to aggregate multiple rows of a file together to get one total for each attribute (a person).

Ex: this is the main data set and connections to a key/date dimension:

I want to create a table in PowerBI that looks like this:

I wanted to use the "pivot" feature in PowerQuery, but the "Number" field is a calculated column, which it appears I can't select via the pivot feature.

Any advice, greatly appreciated. TYIA 

[edhans]

@kressb - you keep sharing data via screenshots. I cannot work with that.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[kressb]

@edhans No problem; thanks for all the help! I will repost as a separate question and see if anyone is willing. Appreciate your time.

[edhans · Accepted solution]

@kressb you shared data in your screenshot. I'm just asking you to share it in a usable format that we can copy and paste into Power BI. As for grouping, it is on the Home Ribbon in Power Query:

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[kressb]

@edhans thank you very much for your help. The Group By Function worked perfectly to get the "Number".
Now, I am trying to calculate "Average Number MTD". I tried to do it within the "Group By" Function, but it takes the average of each line item instead of the average of the sum.
I've tried:

1. Average#MTD = Averagex(values(Table[Date].[Date], [Number]) - returns the Number for each Month 
2. Average#MTD= CALCULATE(AVERAGEX(Table,Table[Number]),Filter(Table,Table[Date].[Date]<=EARLIER(Table[Date].[Date]))) - gives me the average by date, but not to specific person (UuniqueID2). 

   Do I just need another filter here? 

Info to help others:
I needed the original table and the one done via "Group By" (I saw this issue in multiple other questions, but no solution given that would work for me).
I ended up right-clicking on the original table in PowerQuery, selecting Duplicate, renaming it, and then doing the "Group By" function on the duplicate. 

[edhans]

Can you share data so we don't have to key stuff. See links below. Also, is the "Number" column a calculated column in DAX, or a Custom Column in Power Query? 

If the former, can you push it back to Power Query there? As a rule you want to do shaping and modling with Power Query, not DAX. In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
SQLBI Video on Measures vs Calculated Columns

If it is in Power Query, again, sharing data will help. This is not a pivot operation, but a grouping operation and it should work fine.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[kressb]

@edhans This is a calculated column in Dax:
 (it does not exist in the PowerQuery) 

Is there a way to create a "Custom Column in PowerQuery"?

For reference, all the source data is just excel spreadsheets.

Calculated Column was chosen for 2 reasons:

1. Request was for the End-User to not have to deal with formulas/etc. So coding the "Number" equation into excel was not ideal.

2. If I use a "Measure" it seems I cannot reference that # in other "Measures" that I create. However, PowerBI will let me reference calculated columns in "measures" when I need to use them for additional transformations.

Would you mind providing more information on how I utilize a "grouping operation" to get the results that I want?
Thank you!

[edhans]

You can usually do the math in Power Query - usually. I'd need to see the formula.

You can reference measures in other measures.

Unit Price = 
SUMX(
   Sales,
   DIVIDE(
      [Gross Sales],
      Sales[Quantity]
   )
)

There I am calculating the unit price by dividing the Gross Sales measure by the unit price, iterating over the sales table.

I would be more than happy to help. But I need data. And the formula for your calculated column. I'll respost the links on getting help.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[kressb]

@edhans sorry, should have addressed this in the first response. unfortunately my organization does not allow me to utilize onedrive, dropbox, or any other means to "share" data. 

If you can point me in a direction of what you meant by "a grouping operation" I'm happy to investigate further myself.. I just don't know what that means.