Unpivoting and Pivoting Table by using DAX

[suparnababu8]

Hi Fabric Community,
I have a table as metioned below...

This is my Input table to Power BI

Region_East	Region_North	Region_South	Region_West	Country_East	Country_North	Country_South	Country_West	State_East	State_North	State_South	State_West	City_East	City_North	City_South	City_West	Street_East	Street_North	Street_South	Street_West
200	400	250	340	400	250	340	250	200	400	250	340	400	250	340	250	400	250	340	250
400	300	100	120	300	100	120	100	400	300	230	120	300	100	220	100	560	100	120	100
235	200	130	230	230	150	400	200	250	200	150	340	200	340	430	235	200	570	150	650
450	300	350	200	300	350	200	350	450	300	350	200	300	350	200	400	300	350	200	350
1285	1200	830	890	1230	850	1060	900	1300	1200	980	1000	1200	1040	1190	985	1460	1270	810	1350

I need result table as mentioned below

Directions	Locations	Sales
East	Region	1285
East	Country	1230
East	State	1300
East	City	1200
East	Street	1460
North	Region	1200
North	Country	850
North	State	1200
North	City	1040
North	Street	1270
South	Region	830
South	Country	1060
South	State	980
South	City	1190
South	Street	810
West	Region	890
West	Country	900
West	State	1000
West	City	985
West	Street	1350

I don't want to use Power Query editor for this excersize.. 

I need solution for this by using DAX table

Thanks in advance

@comms  @lbendlin @Daniel @cst_bi  @Anonymous  @JDavid 

[Anonymous · Accepted solution]

Hi @suparnababu8 ,

I created a sample pbix fie(see the attachment), it include both DAX and Power Query Editor methods. Please check if that is what you want.

DAX:

ResultTable =
VAR _tab =
    UNION (
        SELECTCOLUMNS (
            InputTable,
            "Directions", "East",
            "Locations", "Region",
            "Sales", [Region_East]
        ),
        SELECTCOLUMNS (
            InputTable,
            "Directions", "East",
            "Locations", "Country",
            "Sales", [Country_East]
        ),
        SELECTCOLUMNS (
            InputTable,
            "Directions", "East",
            "Locations", "State",
            "Sales", [State_East]
        ),
        SELECTCOLUMNS (
            InputTable,
            "Directions", "East",
            "Locations", "City",
            "Sales", [City_East]
        ),
        SELECTCOLUMNS (
            InputTable,
            "Directions", "East",
            "Locations", "Street",
            "Sales", [Street_East]
        ),
        SELECTCOLUMNS (
            InputTable,
            "Directions", "North",
            "Locations", "Region",
            "Sales", [Region_North]
        ),
        SELECTCOLUMNS (
            InputTable,
            "Directions", "North",
            "Locations", "Country",
            "Sales", [Country_North]
        ),
        SELECTCOLUMNS (
            InputTable,
            "Directions", "North",
            "Locations", "State",
            "Sales", [State_North]
        ),
        SELECTCOLUMNS (
            InputTable,
            "Directions", "North",
            "Locations", "City",
            "Sales", [City_North]
        ),
        SELECTCOLUMNS (
            InputTable,
            "Directions", "North",
            "Locations", "Street",
            "Sales", [Street_North]
        ),
        SELECTCOLUMNS (
            InputTable,
            "Directions", "South",
            "Locations", "Region",
            "Sales", [Region_South]
        ),
        SELECTCOLUMNS (
            InputTable,
            "Directions", "South",
            "Locations", "Country",
            "Sales", [Country_South]
        ),
        SELECTCOLUMNS (
            InputTable,
            "Directions", "South",
            "Locations", "State",
            "Sales", [State_South]
        ),
        SELECTCOLUMNS (
            InputTable,
            "Directions", "South",
            "Locations", "City",
            "Sales", [City_South]
        ),
        SELECTCOLUMNS (
            InputTable,
            "Directions", "South",
            "Locations", "Street",
            "Sales", [Street_South]
        ),
        SELECTCOLUMNS (
            InputTable,
            "Directions", "West",
            "Locations", "Region",
            "Sales", [Region_West]
        ),
        SELECTCOLUMNS (
            InputTable,
            "Directions", "West",
            "Locations", "Country",
            "Sales", [Country_West]
        ),
        SELECTCOLUMNS (
            InputTable,
            "Directions", "West",
            "Locations", "State",
            "Sales", [State_West]
        ),
        SELECTCOLUMNS (
            InputTable,
            "Directions", "West",
            "Locations", "City",
            "Sales", [City_West]
        ),
        SELECTCOLUMNS (
            InputTable,
            "Directions", "West",
            "Locations", "Street",
            "Sales", [Street_West]
        )
    )
RETURN
    SUMMARIZE (
        _tab,
        [Directions],
        [Locations],
        "Sales",
            SUMX (
                FILTER (
                    _tab,
                    [Directions] = EARLIER ( [Directions] )
                        && [Locations] = EARLIER ( [Locations] )
                ),
                [Sales]
            )
    )

Power Query Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVBBDsAgCPuLZw8K4txbjP//xoLo4EDMlhiipZbS3gOkFGIoswJxxeIjcv/L9/ER++rgrFkq+Eg2M6UL6PPB8Kl6OjwZkN5NMqreUrW+jfvFt5sks/9SUGW6lF/3zqSO0ag6iLj4zC8HHZ6coZEEwWibbtst2ciDJKKZ2r2zSfrndMZ4AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Region_East = _t, Region_North = _t, Region_South = _t, Region_West = _t, Country_East = _t, Country_North = _t, Country_South = _t, Country_West = _t, State_East = _t, State_North = _t, State_South = _t, State_West = _t, City_East = _t, City_North = _t, City_South = _t, City_West = _t, Street_East = _t, Street_North = _t, Street_South = _t, Street_West = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Region_East", Int64.Type}, {"Region_North", Int64.Type}, {"Region_South", Int64.Type}, {"Region_West", Int64.Type}, {"Country_East", Int64.Type}, {"Country_North", Int64.Type}, {"Country_South", Int64.Type}, {"Country_West", Int64.Type}, {"State_East", Int64.Type}, {"State_North", Int64.Type}, {"State_South", Int64.Type}, {"State_West", Int64.Type}, {"City_East", Int64.Type}, {"City_North", Int64.Type}, {"City_South", Int64.Type}, {"City_West", Int64.Type}, {"Street_East", Int64.Type}, {"Street_North", Int64.Type}, {"Street_South", Int64.Type}, {"Street_West", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Index] <> 5)), 
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Filtered Rows", {"Region_East", "Region_North", "Region_South", "Region_West", "Country_East", "Country_North", "Country_South", "Country_West", "State_East", "State_North", "State_South", "State_West", "City_East", "City_North", "City_South", "City_West", "Street_East", "Street_North", "Street_South", "Street_West"}, "Attribute", "Sales"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Only Selected Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Locations", "Directions"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Locations", type text}, {"Directions", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Directions", "Locations"}, {{"Sales", each List.Sum([Sales]), type number}})
in
    #"Grouped Rows"

Best Regards

[suparnababu8]

from Input table bottom row values representing Total of each column