Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |