Creating a table based on another table Column Value

[fmigg]

Hi guys! 

I'm new on Power BI and DAX, such in this community

Currently I have a dataset with the following structure:
Table 1

In which Revenues column collect the correct revenue according Phase column. Also each ID could have different types like (A Banking, A Software, B Banking etc)

What I'd like to do is to create a new table with the correct Revenue according the maximum value of Phase on each ID+Type combination, something like this.
Table 2

 

Currently what I was doing was to add in the original table a new column called [ID + Type] which it just concatenates ID and Type: 

Then, I tried to create a new table using SUMMARIZEDCOLUMNS:

Table2 = SUMMARIZECOLUMNS(
	Table1[ID + Type],
	"ID", SELECTEDVALUE(Table1[ID]),
	"Budget", CALCULATE(SELECTEDVALUE(Table1[Revenue]),SELECTEDVALUE(Table1[Phase]) = MAX(Table1[Phase]))
)

But this is not working, since MAX(Table1[Phase]) is always getting a 6 instead the max Phase number for each [ID + Type] value.

Is this possible get solved in DAX?

 P.S. I'm new in adding this questions to communities, if something need to be corrected such title or content please let me now and I will do the corrections 🙂 

------------------------------------------ UPDATE 

I forgot to mention, if the value of Phase is the maximum (6 in this example, there is no greater value on Phase than 6) it should SUM all revenues for each ID + Type, for example: 

For ASoftware there are two Revenues has 6, so it must be SUM in the created table.

[v-danhe-msft]

Hi @fmigg,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[siemdevlieger]

Hi, 

I am trying to do the same. Where can I add a new table based on another table?

Looking at the PBIX it was not done in the query editor right?

Struggling with this, hope someone can give an easy explanation here.

Thanks in advance!

Regards,

Siem

[v-danhe-msft · Accepted solution]

Hi @fmigg,

Based on my test, you could refer to below steps:

Create a calculated column in your row table:

filtered value = IF([Phase]=CALCULATE(MAX('Table1'[Phase]),FILTER('Table1','Table1'[Column]=EARLIER(Table1[Column]))),[Revenue],0)

Create a new table:

New Table = SUMMARIZE('Table1','Table1'[Column],'Table1'[ID],"A",CALCULATE(SUM('Table1'[filtered value])))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He