Forum Discussion
Combine Column
Hi good day, pls can anyone help me on my table how can i get the result i required.
DESIRED OUTPUT
Thank you
Hi AllanBerces
While this is very possible in DAX, the calculation won't update with new columns as each column has to be manually referenced and if a colum that's been referenced is deleted, the calc table will return an error.
UnpivotDAX = VAR _job = DISTINCT ( SourceTable[Job] ) VAR _jobyr = CROSSJOIN ( _job, SELECTCOLUMNS ( { 2024, 2025 }, "Year", [Value] ) ) VAR _earned = ADDCOLUMNS ( _jobyr, "Earned", VAR _job = [Job] VAR _2024 = SUMX ( FILTER ( SourceTable, [Job] = _job ), [Earned_2024] ) VAR _2025 = SUMX ( FILTER ( SourceTable, [Job] = _job ), [Earned_2025] ) RETURN SWITCH ( [Year], 2024, _2024, 2025, _2025 ) ) RETURN FILTER ( _earned, NOT ( ISBLANK ( [Earned] ) ) )
7 Replies
Hi AllanBerces
While this is very possible in DAX, the calculation won't update with new columns as each column has to be manually referenced and if a colum that's been referenced is deleted, the calc table will return an error.
UnpivotDAX = VAR _job = DISTINCT ( SourceTable[Job] ) VAR _jobyr = CROSSJOIN ( _job, SELECTCOLUMNS ( { 2024, 2025 }, "Year", [Value] ) ) VAR _earned = ADDCOLUMNS ( _jobyr, "Earned", VAR _job = [Job] VAR _2024 = SUMX ( FILTER ( SourceTable, [Job] = _job ), [Earned_2024] ) VAR _2025 = SUMX ( FILTER ( SourceTable, [Job] = _job ), [Earned_2025] ) RETURN SWITCH ( [Year], 2024, _2024, 2025, _2025 ) ) RETURN FILTER ( _earned, NOT ( ISBLANK ( [Earned] ) ) )Hi danextian thank you very much for the reply, very much appreciated. work as i need.
Hi AllanBerces
You can do this very easily using Power Query.
What I did was make a reference to the Source Table, select the Job column and then ‘Unpivot other columns’.
After renaming the Attribute column to Year and removing "Earned_" from the Year column by using "Replace Values" and changed the type, your data should be in a usable format for DAX.
Let me know if you have any questions.
Hi gmsamborn thank you for the reply, but my table come from summarized table
Hi AllanBerces
I said it'd be easy in Power Query. DAX is a different question.
I'm curious how you built the summarized table to have that format. (Also, the format of the data you are summarizing.)
Would it be possible to remove "Earned_" from the column headers of your summary table?
