DAX Calculated Column

[Anonymous]

Hi All,

I have a situation where in have to represent the data horizontally instead of vertical representation.

Please refer the below table. I have the data currently as mentioned below.

Month'Year	ID	Forecasted Gross	Forecasted Net
April'2021	123	4000	4000
May'2021	123	25000	20000
June'2021	123	45000	30000
July'2021	123	6000	5000
Aug'2021	123	1500	1200
Sep'2021	123	12000	10000

My Expected Result is that the data can be seen horizontally. like in the report i can present it like below:

ID	April'2021ForecastedGross	April'2021ForecastedNet	May'2021Forecasted GRoss	May'2021ForecastedNEt
123	4000	4000	25000	20000

Is there any DAX calculation which I can use?

Regards,

Himanshu

[Anonymous]

Hi  @Anonymous  ,

Here are the steps you can follow：

1. Enter the power query through Transform data, select Advanced Editor, copy and paste, and done.

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoysyJKTUwMDI3MjAyVNJRMjQyBpImBgYGMCpWJ1rJN7ESiyojU4gyIwOYOq/SvFRsxkEVGiMpzMFmohlEnSlMmWNpOhZVhqZgVYZGUFXBqQXYVBlBDDOEWBoLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Month'Year" = _t, ID = _t, #"Forecasted Gross" = _t, #"Forecasted Net" = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month'Year", type text}, {"ID", Int64.Type}, {"Forecasted Gross", Int64.Type}, {"Forecasted Net", Int64.Type}}),

    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Forecasted Gross", "Forecasted Net"}, "Attribute", "Value"),

    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"ID", "Forecasted Net", "Attribute", "Value"}, "Attribute.1", "Value.1"),

    #"Unpivoted Columns2" = Table.UnpivotOtherColumns(#"Unpivoted Columns1", {"ID", "Attribute", "Value", "Attribute.1", "Value.1"}, "Attribute.2", "Value.2"),

    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns2", "Custom", each [Value]),

    #"Merged Columns" = Table.CombineColumns(#"Added Custom",{"Value", "Attribute.1"},Combiner.CombineTextByDelimiter("#(tab)", QuoteStyle.None),"Merged"),

    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Custom", "Attribute.2"},Combiner.CombineTextByDelimiter("#(tab)", QuoteStyle.None),"Merged.1"),

    #"Pivoted Column" = Table.Pivot(#"Merged Columns1", List.Distinct(#"Merged Columns1"[Merged]), "Merged", "Value.1", List.Sum),

    #"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[Merged.1]), "Merged.1", "Value.2", List.Sum),

    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column1",{"Attribute"}),

    #"Filled Down" = Table.FillDown(#"Removed Columns",{"April'2021#(tab)Forecasted Gross"}),

    #"Filled Up" = Table.FillUp(#"Filled Down",{"April'2021#(tab)Forecasted Gross", "May'2021#(tab)Forecasted Gross"}),

    #"Filled Down1" = Table.FillDown(#"Filled Up",{"May'2021#(tab)Forecasted Gross", "June'2021#(tab)Forecasted Gross"}),

    #"Filled Up1" = Table.FillUp(#"Filled Down1",{"June'2021#(tab)Forecasted Gross"}),

    #"Filled Down2" = Table.FillDown(#"Filled Up1",{"July'2021#(tab)Forecasted Gross", "Aug'2021#(tab)Forecasted Gross", "Sep'2021#(tab)Forecasted Gross", "April'2021#(tab)Forecasted Net", "Aug'2021#(tab)Forecasted Net", "July'2021#(tab)Forecasted Net", "June'2021#(tab)Forecasted Net", "May'2021#(tab)Forecasted Net", "Sep'2021#(tab)Forecasted Net"}),

    #"Filled Up2" = Table.FillUp(#"Filled Down2",{"July'2021#(tab)Forecasted Gross", "Aug'2021#(tab)Forecasted Gross", "Sep'2021#(tab)Forecasted Gross", "April'2021#(tab)Forecasted Net", "Aug'2021#(tab)Forecasted Net", "July'2021#(tab)Forecasted Net", "June'2021#(tab)Forecasted Net", "May'2021#(tab)Forecasted Net", "Sep'2021#(tab)Forecasted Net"}),

    #"Removed Duplicates" = Table.Distinct(#"Filled Up2"),

    #"Reordered Columns" = Table.ReorderColumns(#"Removed Duplicates",{"ID", "April'2021#(tab)Forecasted Gross", "April'2021#(tab)Forecasted Net", "May'2021#(tab)Forecasted Gross", "May'2021#(tab)Forecasted Net", "June'2021#(tab)Forecasted Gross", "June'2021#(tab)Forecasted Net", "July'2021#(tab)Forecasted Gross", "July'2021#(tab)Forecasted Net", "Aug'2021#(tab)Forecasted Gross", "Aug'2021#(tab)Forecasted Net", "Sep'2021#(tab)Forecasted Gross", "Sep'2021#(tab)Forecasted Net"})

in

    #"Reordered Columns"

2. Result.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Anonymous  I actualyy have many columns in my "View".

Please see below screenshot. I have a column now "Merged" which I want to pivot with the Forecasted Net and Forecasted Gross so that I can represent my data horizontally. But the pivot takes a lot of time as I have around 8 million records in my dataset. Can i do something else to get this done?

Can you tell me a step where I can just combine the Merged column and Forecasted Gross column and represent the data horizontally as in columns.

Regards,

Himanshu

[Anonymous]

@Jihwan_Kim Can you help me here?

Regards,

Himanshu

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your message.

I have tried many things to achieve your goal. However, the normal exporting function does not seem to give the result you want.
I think one way is to use external tools (Analyze in Excel).

If you click this in Power BI, the below window (picture in the below) will open. The Excel window will open, and you can create your own style table in excel directly.

If your purpose is to export the wide-style-matrix visualization to excel, I think one way is to create the visualization directly by using "Analyze in Excel" function, and it might be helpful.

 

[Anonymous]

@Jihwan_Kim  Yes I tried this option and it works fine but my issue is I've embed the report in my application and there I dont have the option of "Analyse in Excel" so i was trying to figure out if I can do something at the desktop level.

[amitchandak]

@Anonymous , Id on row and MonthYear on the column and Forecasted Gross , Forecasted Net on values of the Matrix

[Anonymous]

@amitchandak  The issue is that while exporting the data it comes vertically only even if I use Matrix so thats the reason I want to create columns horizontally. Also i have few other columns as well in my report and showing them on Matrix is not looking nice. 

The want the export also to be horizontal and that can be achieved only if I create columns with the expected data.

[Anonymous]

@amitchandak If there's a way that my Export also shows the data as in the Matrix dashboard then also I can manage. But the export shows data vertically only and thats the issue for Client. 

Really need help on this as its an issue for the client. 

Regards,

Himanshu