Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Display Measures in Rows and Fixed values (TY, LY, Var %) in Columns

Hi All,

 

What i am trying to achieve i thought would be quite straightforward, but seems not to be the case!

 

I am importing data into PBI using some MDX against Analyses Services. I am retuning multiple measures in this single Query as well as some descriptive columns too. Below is an example of some of the Measures/Columns i am importing into PBI:

 

- Date (Descriptive Attribute)

- Region (Descriptive Attribute)

- TY YTD Sales (Numerical Attribute)

- TY PTD Sales (Numerical Attribute)

- TY WTD Sales (Numerical Attribute)

- TY Daily Sales (Numerical Attribute)

- LY YTD Sales (Numerical Attribute)

- LY PTD Sales (Numerical Attribute)

- LY WTD Sales (Numerical Attribute)

- LY Daily Sales (Numerical Attribute)

- ...

- YTD Sales Online vs LY Value £ (Numerical Attribute)

- YTD Sales Online vs LY Value % (Numerical Attribute)

 

What i am looking to achieve is:

1. To display the Measures in the Rows of the Matrix.

2. To display This Year (TY), Last Year (LY) and Variance % in the Columns

 

So the display i am trying to achieve is the below:

 

 TYLYVar %
Daily Sales # #(TY-LY)/LY
WTD Sales # #(TY-LY)/LY
PTD Sales # #(TY-LY)/LY
YTD Sales # #(TY-LY)/LY


Note: I know Power BI offers the "Show on rows" option to be enabled, which is great)!

However, the struggle is with displaying the fixed values for all measures in the columns.

 

I have come up with a solution that partially works, issue is with the Variance %, which would appreciate any suggestions on how to resolve the issue and more importantly, finding a better way to achieve this!

 

1. In Power Query, in my Dataset i created a 'custom column' called 'Variance %':

 

 

([Sales Online] - [LY Sales Online]) / [LY Sales Online]

2. I Unpivoted Columns (all measures), which created a column listing all Measures:

 

 

From this:

 

DateRegion NameYTD SalesPTD SalesWTD Sales OnlineSales Online
09/09/2018Online####

 

To this:

 

DateRegion NameMeasuresValue
09/09/2018OnlineYTD Sales#
09/09/2018OnlinePTD Sales#
09/09/2018OnlineWTD Sales Online#
09/09/2018OnlineSales Online#

 

3. I then added a conditional column called "Category" to identify each Measure Attribute as either: TY, LY, Var £ or Var %. As the below table shows:

 

DateRegion NameMeasuresValueCategory
09/09/2018OnlineTY YTD Sales#TY
09/09/2018OnlineTY PTD Sales#TY
09/09/2018OnlineTY WTD Sales#TY
09/09/2018OnlineTY Daily Sales#LY
09/09/2018OnlineLY YTD Sales#LY
09/09/2018OnlineLY PTD Sales#LY
09/09/2018OnlineLY WTD Sales#LY
09/09/2018OnlineLY Daily Sales#LY
09/09/2018OnlineSales Online vs LY Value £#Var £
09/09/2018OnlineWTD Sales Online vs LY Value £#Var £
09/09/2018OnlinePTD Sales Online vs LY Value £#Var £
09/09/2018OnlineYTD Sales Online vs LY Value £#Var £
09/09/2018OnlineSales Online vs LY Value %#Var %
09/09/2018OnlineWTD Sales Online vs LY Value %#Var %
09/09/2018OnlinePTD Sales Online vs LY Value %#Var %
09/09/2018OnlineYTD Sales Online vs LY Value %#Var %

 

IMPORTANT:

- The first issue with the above is that the Column "Value" should be a of Type Decimal Number (TY, LY, Var £) and of Type Percentage (Var %). This cannot be done with an Calculated Measure in DAX.

 

4. Once i load the above data, i then right click on "Measures" --> New Groups:

 

Created the Groups:

 

Group 1 = 'TY Daily Sales', 'LY Daily Sales', 'Sales Online vs LY Value %'

Group 2 = 'TY WTD Sales', 'LY WTD Sales', 'WTD Sales Online vs LY Value %'

....

 

5. I then drag and drop the Matrix Visual:

- Rows = Measure Groups 

- Columns = Category

- Values = Value

 

The result i get is the following:

 

 TYLYVar %
Group 1Correct Correct Wrong
Group 2Correct Correct 

Wrong

 

The 'TY' and 'LY' Value is displaying as expected, however the Vari % is not!

 

Any help on this would be very appreciated.

 

Laz

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi Anonymous

    Based on my knowledge, it is not possible for a column or measure to contains two kinds of data types.

    When you add the column or measure [Value] to the Value field of the Matrix visual, even it is listed under different columns visually, but in fact [Value] can only exsit in one column or one measure.

    Here is a workaround which i list columns or measures in the Table visual, finally the Table visual is like below.

    Original table

    Final Table visual

    Code in Advanced editor

    let
    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\9\9.13\9.13.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Region Name", type text}, {"TY YTD Sales", Int64.Type}, {"TY PTD Sales", Int64.Type}, {"TY WTD Sales Online", Int64.Type}, {"TY Daily Sales", Int64.Type}, {"LY YTD Sales", Int64.Type}, {"LY PTD Sales", Int64.Type}, {"LY WTD Sales Online", Int64.Type}, {"LY Daily Sales", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Region Name"}, "Attribute", "Value"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Attribute", "Attribute - Copy"),
    #"Split Column by Position" = Table.SplitColumn(#"Duplicated Column", "Attribute - Copy", Splitter.SplitTextByPositions({0, 3}, false), {"Attribute - Copy.1", "Attribute - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute - Copy.1", type text}, {"Attribute - Copy.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[#"Attribute - Copy.1"]), "Attribute - Copy.1", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Date", Order.Ascending}, {"Attribute - Copy.2", Order.Ascending}, {"Attribute", Order.Ascending}}),
    #"Filled Up" = Table.FillUp(#"Sorted Rows",{"TY "}),
    #"Filled Down" = Table.FillDown(#"Filled Up",{"LY "}),
    #"Removed Duplicates" = Table.Distinct(#"Filled Down", {"TY ", "Date"})
in
    #"Removed Duplicates"

     

    Best Regards

    Maggie

     

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi Anonymous

    Based on my knowledge, it is not possible for a column or measure to contains two kinds of data types.

    When you add the column or measure [Value] to the Value field of the Matrix visual, even it is listed under different columns visually, but in fact [Value] can only exsit in one column or one measure.

    Here is a workaround which i list columns or measures in the Table visual, finally the Table visual is like below.

    Original table

    Final Table visual

    Code in Advanced editor

    let
    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\9\9.13\9.13.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Region Name", type text}, {"TY YTD Sales", Int64.Type}, {"TY PTD Sales", Int64.Type}, {"TY WTD Sales Online", Int64.Type}, {"TY Daily Sales", Int64.Type}, {"LY YTD Sales", Int64.Type}, {"LY PTD Sales", Int64.Type}, {"LY WTD Sales Online", Int64.Type}, {"LY Daily Sales", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Region Name"}, "Attribute", "Value"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Attribute", "Attribute - Copy"),
    #"Split Column by Position" = Table.SplitColumn(#"Duplicated Column", "Attribute - Copy", Splitter.SplitTextByPositions({0, 3}, false), {"Attribute - Copy.1", "Attribute - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute - Copy.1", type text}, {"Attribute - Copy.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[#"Attribute - Copy.1"]), "Attribute - Copy.1", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Date", Order.Ascending}, {"Attribute - Copy.2", Order.Ascending}, {"Attribute", Order.Ascending}}),
    #"Filled Up" = Table.FillUp(#"Sorted Rows",{"TY "}),
    #"Filled Down" = Table.FillDown(#"Filled Up",{"LY "}),
    #"Removed Duplicates" = Table.Distinct(#"Filled Down", {"TY ", "Date"})
in
    #"Removed Duplicates"

     

    Best Regards

    Maggie

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hey v-juanli-msft,

       

      Thank you so much!

       

      Went through the M Code you shared and applied it to my solution and it works great!

      One question i have is,in my solution i have multiple measures such as:

       

      - Sales (Decimal Number)

      - Product Margin (Decimal Number)

      - Nr of Transactions (Whole Number)

      - Product Margin % (Percentage)

       

      When i use this solution for measures with Data Types of: Decimal Number or Whole Number everything works great.

       

      However, when i try to include 'Product Margin %' this is when it gets messy.

      When i add 'Product Margin %' to a visual such as a Matrix, and go to a hgiher level detail, as you can imagine the percentage value is being summed up, which displays incorrect values, however, when i drill down to the lowest level, i get the expected result.

       

      If you have any pointers on how you would approach this, would much appreciate it.

       

      Thanks,

       

      Laz