Forum Discussion
DAX create a table pivot like
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,
5 Replies
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"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,
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
