Forum Discussion
Maybe a DAX solution? Or Power Query? Not sure
Hi Folks,
Wondering if you guys can help with this. I'm not sure if this solution requires DAX or further manipulation in Power Query.
DATA
EXPECTED RESULT
Thanks in advance!
Hi peterso ,
You can create this calculated column on table2 to get the expected result:
Column = IF ( 'Table 2'[Phases] IN DISTINCT ( 'Table 1'[Phases] ), LOOKUPVALUE ( 'Table 1'[Value A], 'Table 1'[Projects], [Projects], 'Table 1'[Phases], [Phases] ), CALCULATE ( SUM ( 'Table 1'[Value B] ), FILTER ( ALL ( 'Table 1' ), 'Table 1'[Projects] = EARLIER ( 'Table 2'[Projects] ) ) ) )Attached a sample file in the below, hopes to help you.
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hello @peterso
it may be your solution looks like that:
Sum of Value A = SUM('Table'[Value A]) Sum of Value B = IF(NOT(ISINSCOPE('Table'[Phases])),SUM('Table'[Value B]),BLANK())With kind greetings from the city where the legend of the 'Pied Piper de Hamelin' is at home
FrankAT (Proud to be a Datanaut)
7 Replies
peterso , you need to create an aggregated table in power query
Projects, sum of value B , which you have to call Value A, You need to add new column phase in that
Phase = [Projects] & "Project Sum B''
Now merge these table. Projects,Value A, Phase should merge
https://radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
I apologize I don't quite understand. The Aggregate button is greyed out for me in Power Query
peterso - So there may be some way to do this in Power Query, ImkeF or edhans might know. But if you had those two tables in your data model you could do something like the following untested code because you didn't paste anything that I could copy and paste but instead pasted pictures and I don't feel like typing all that data right now. So, if you get syntax errors, that's on you. 🙂
Table = VAR __Table = ADDCOLUMNS( ADDCOLUMNS( 'Table 2', "Column1",LOOKUPVALUE('Table 1'[Value A],'Table 1'[Project],[Project],'Table 1'[Phase],[Phase]) ), [Column],IF(ISBLANK([Column1]),SUMX(FILTER(ALL('Table 1'),[Project]=EARLIER([Project])&&[Phase]=EARLIER([Phase])),[Value B]),[Column1]) ) RETURN SELECTCOLUMNS(__Table,"Project",[Project],"Phase",[Phase],"Column",[Column])
Hi peterso ,
You can create this calculated column on table2 to get the expected result:
Column = IF ( 'Table 2'[Phases] IN DISTINCT ( 'Table 1'[Phases] ), LOOKUPVALUE ( 'Table 1'[Value A], 'Table 1'[Projects], [Projects], 'Table 1'[Phases], [Phases] ), CALCULATE ( SUM ( 'Table 1'[Value B] ), FILTER ( ALL ( 'Table 1' ), 'Table 1'[Projects] = EARLIER ( 'Table 2'[Projects] ) ) ) )Attached a sample file in the below, hopes to help you.
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
peterso - can you provide data? You can do a calculated column as v-yingjl has shown, but I generally advise against that as they have numerous issues and should be used as a last resort. However, I cannot tinker with this in Power Query until I have some sample data to work with.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI ForumIn 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 ColumnsHello @peterso
it may be your solution looks like that:
Sum of Value A = SUM('Table'[Value A]) Sum of Value B = IF(NOT(ISINSCOPE('Table'[Phases])),SUM('Table'[Value B]),BLANK())With kind greetings from the city where the legend of the 'Pied Piper de Hamelin' is at home
FrankAT (Proud to be a Datanaut)
