Create table with distinct values from column of original table

[Anonymous]

Hello,

I have an initial table with a column for Accounts, Balance and Date. I need to extract the average per month of the Balances per Account, however I will need to further cross this information with other tables, and therefore I need this information in a new table and not using DAX.

I have already managed to create a column with the average of each Month/Account (“Balance_Avg”) and another one with the respective month (“Date_Month”, I basically transformed all the dates into the first day of the respective month).

I now need a new table where I have only every unique occurrence from “Account” and “Balance_Avg” (otherwise when I use this column as a value it will sum all the ocurrences along with other issues while combining this table).

I had already tried the following approaches:

• In Query Editor removing duplicates based on the “Account” and “Balance_Avg”, however, given that the “Balance_Avg” column was created using DAX the fact that I make this step in Query Editor the elimination of the duplicates occur before the calculation of the Average, thus defeating the purpose.
• The following solution, however I have already tried to apply this formula, but it simply replicates every value, I can’t seem to make it work.
  https://community.powerbi.com/t5/Desktop/Create-a-new-table-with-distinct-values/td-p/534662

I have already been stuck in the problem for quite a while, any help would be great.

Thank you in advance for your time.   

Original	Original	Original	Calculated	Calculated (w/ DAX)
Account	Balance	Date	Date_Month	Balance_Avg
A	30	01/01/2019	01/01/2019	40
A	60	02/01/2019	01/01/2019	40
A	30	03/01/2019	01/01/2019	40
A	60	01/02/2019	01/02/2019	50
A	30	02/02/2019	01/02/2019	50
A	60	03/02/2019	01/02/2019	50
B	180	01/01/2019	01/01/2019	90
B	60	02/01/2019	01/01/2019	90
B	30	03/01/2019	01/01/2019	90
B	30	01/02/2019	01/02/2019	30
B	30	02/02/2019	01/02/2019	30
B	30	03/02/2019	01/02/2019	30

Objective
Account	Date_Month	Balance
A	01/01/2019	40
A	01/02/2019	50
B	01/01/2019	90
B	01/02/2019	30

[rocky09 · Accepted solution]

something like this?

New Table = SUMMARIZE(OriginalTable,OriginalTable[Account],OriginalTable[Date_Month],OriginalTable[Balance_Avg])

[Anonymous]

Thank you so much, I was certain that the solution would involve DISTINCT and some sort of LOOKUPVALUE and didn't even assumed to use the simple form of SUMMARIZE.

Once again thank you very much, it worked perfectly.