Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

DAX Calculated Column

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'YearIDForecasted GrossForecasted Net
April'202112340004000
May'20211232500020000
June'20211234500030000
July'202112360005000
Aug'202112315001200
Sep'20211231200010000

 

 

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

IDApril'2021ForecastedGrossApril'2021ForecastedNetMay'2021Forecasted GRossMay'2021ForecastedNEt
123400040002500020000

 

 

Is there any DAX calculation which I can use?

 

Regards,

Himanshu

Need Help

8 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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