DAX create a table pivot like

[Jlbaenlo]

I have the following case,

I have a table containing the following info :

BusField, Region, Sales1, Sales2, Sales3, Year

B1, R1, 100, 200, 300, 2020

B1, R1, 200, 300, 200, 2021 

B1, R1, 100, 200, 300, 2022

B1, R1, 200, 300, 200, 2023 

B1, R2, 200, 200, 100, 2020

B1, R2, 300, 300, 200, 2021 

B1, R2, 100, 100, 200, 2022

B1, R2, 200, 200, 100, 2023 

and I want to pivot it using DAX to look like

BusField, Region, Type, 2020,2021,2022,2023 etc..

where in the column type, we'll find Sales1, Sales2, Sales3

and under each year, we'll find the sum per BusField, Region, Type

How can I do that ?

Many thanks,

[Jlbaenlo]

Original TEST table

Business Field	Region	Year	Sales1	Sales2	Sales3
THY	B	2023	10	20	30
THY	B	2024	5	10	1
THY	B	2025	6	8	9
THY	B	2026	7	5	10
CAR	B	2023	11	21	31
CAR	B	2024	6	11	2
CAR	B	2025	7	9	10
CAR	B	2026	8	8	5
after transform
Business Field	SD Region BF	Type	2023	2024	2025
CAR	B	Sales1	11	6	7
CAR	B	Sales2	21	11	9
CAR	B	Sales3	31	2	10
THY	B	Sales1	10	5	6
THY	B	Sales2	20	10	8
THY	B	Sales3	30	1	9

[Jlbaenlo]

I found a way in the end :

TransformedTable =
VAR __T1 =ADDCOLUMNS(
SUMMARIZE(Test,'Test'[Business Field],'Test'[SD Region BF]),
"Type", "Sales1",
"2023" , CALCULATE( MAX('Test'[Sales1]) , TREATAS( {2023},Test[YEAR])),
"2024" , CALCULATE( MAX('Test'[Sales1]) , TREATAS( {2024},Test[YEAR])),
"2025" , CALCULATE( MAX('Test'[Sales1]) , TREATAS( {2025},Test[YEAR]))
)
VAR __T2 =ADDCOLUMNS(
SUMMARIZE(Test,'Test'[Business Field],'Test'[SD Region BF]),
"Type", "Sales2",
"2023" , CALCULATE( MAX('Test'[Sales2]) , TREATAS( {2023},Test[YEAR])),
"2024" , CALCULATE( MAX('Test'[Sales2]) , TREATAS( {2024},Test[YEAR])),
"2025" , CALCULATE( MAX('Test'[Sales2]) , TREATAS( {2025},Test[YEAR]))
)
VAR __T2 =ADDCOLUMNS(
SUMMARIZE(Test,'Test'[Business Field],'Test'[SD Region BF]),
"Type", "Sales3",
"2023" , CALCULATE( MAX('Test'[Sales3]) , TREATAS( {2023},Test[YEAR])),
"2024" , CALCULATE( MAX('Test'[Sales3]) , TREATAS( {2024},Test[YEAR])),
"2025" , CALCULATE( MAX('Test'[Sales3]) , TREATAS( {2025},Test[YEAR]))
)
RETURN
UNION(__T1,__T2, __T3)

The only issue now is that I need to hard code the year in my DAX... is there any clever way to treat Year in a list and use it there ?

Many thanks,

BR,

[gmsamborn]

Hi @Jlbaenlo 

I'm not sure how to get around your hard-coded Year problem in DAX.

With the Power Query solution provided by @danextian , this isn't a problem.

Can I ask why you specified that this be done in DAX?

[danextian]

Hi @Jlbaenlo ,

Im unsure how you want your final table to look like as your description is not enought but if you want something like below, here's a sample M code you can paste into a blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVZyMtRRCAJiQwMDHQUjEGEMYRkZgOVRFCHLQxQZKmCqwjTKiBijjNGNMoLLGcANxXCVEdQU/K4yghpgiKwK3VVY7QO5KhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"BusField, Region, Sales1, Sales2, Sales3, Year" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"BusField, Region, Sales1, Sales2, Sales3, Year", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [#"BusField, Region, Sales1, Sales2, Sales3, Year"] <> null and [#"BusField, Region, Sales1, Sales2, Sales3, Year"] <> ""),
    #"Split Column by Delimiter" = let oldcolumn  = Table.ColumnNames(#"Filtered Rows"){0},
newcolumn = Text.Split(oldcolumn, ","),
newcolumn2 = List.Transform(newcolumn, Text.Trim)
in
Table.SplitColumn(#"Filtered Rows", "BusField, Region, Sales1, Sales2, Sales3, Year", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), newcolumn2),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"BusField", "Region", "Year"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Int64.Type}})
in
    #"Changed Type1"

[Ritaf1983]

Hi @Jlbaenlo 
Can you please show how you need the result to look?