Combine Column

[AllanBerces]

Hi good day, pls can anyone help me on my table how can i get the result i required.

DESIRED OUTPUT

Thank you

[danextian · Accepted solution]

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] ) ) )

[AllanBerces]

Hi @danextian thank you very much for the reply, very much appreciated. work as i need.

[gmsamborn]

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.

AllanBerces.pbix

Let me know if you have any questions.

[AllanBerces]

Hi @gmsamborn thank you for the reply, but my table come from summarized table

[gmsamborn]

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?

[AllanBerces]

Hi @gmsamborn thank you very much for the reply, I understand its easy on the PQ if i used only one table but my on my case. I summarized one of my table then lookup on the other table to get Earned per year and that my current table that i required desired output.

[gmsamborn]

OK @AllanBerces 

It might be possible if the column headers didn't have "Earned_".

Sorry I couldn't help and I'm going to keep an eye on this question since I would like to see the DAX.  I'm sure others will find something that helps.