Add Custom Row with calculated Measures

[Abdalmoamen]

Hello Everyone 

I have created my report which get the data from SSAS (Import Method),

And i Want to add one more row to the results and this row has a calculated values  from the other rows. 

Can i do that in the Advanced editor ? 

This's My Columns :

• Accounts.ID
• AccountType.Account Type
• Basis.Basis
• Chart Of Accounts.Level 02
• Chart Of Accounts.Level 03
• Chart Of Accounts.Level 04
• Chart Of Accounts.Level 05
• Chart Of Accounts.Level 06
• Cost Centers.ID
• JE Type.JE Type
• Posting Status.Posted
• Related to sales.Relatedto Sales
• Scenarios.Scenario Key
• Time.Date
• Time.Month
• Time.Quarter
• Time.Year
• Amount
• IncomeStatementAmount
• Income Exp
• Income Rev
• Budget
• IncomeAmount
• YTD
• Total Sales YTD Last Year
• Variance
• LAST REFRESHED
  This's my Query in Advanced Editor 

let
    Source = AnalysisServices.Database(".", "gs1olap", [TypedMeasureColumns=true, Implementation="2.0"]),
    GeneralLedger1 = Source{[Id="GeneralLedger"]}[Data],
    GeneralLedger2 = GeneralLedger1{[Id="GeneralLedger"]}[Data],
    #"Added Items" = Cube.Transform(GeneralLedger2,
        {
            {Cube.AddAndExpandDimensionColumn, "[Accounts]", {"[Accounts].[ID].[ID]"}, {"Accounts.ID"}},
            {Cube.AddAndExpandDimensionColumn, "[AccountType]", {"[AccountType].[Account Type].[Account Type]"}, {"AccountType.Account Type"}},
            {Cube.AddAndExpandDimensionColumn, "[Basis]", {"[Basis].[Basis].[Basis]"}, {"Basis.Basis"}},
            {Cube.AddAndExpandDimensionColumn, "[Chart Of Accounts]", {"[Chart Of Accounts].[Parent Node Id].[Level 02]", "[Chart Of Accounts].[Parent Node Id].[Level 03]", "[Chart Of Accounts].[Parent Node Id].[Level 04]", "[Chart Of Accounts].[Parent Node Id].[Level 05]", "[Chart Of Accounts].[Parent Node Id].[Level 06]"}, {"Chart Of Accounts.Level 02", "Chart Of Accounts.Level 03", "Chart Of Accounts.Level 04", "Chart Of Accounts.Level 05", "Chart Of Accounts.Level 06"}},
            {Cube.AddAndExpandDimensionColumn, "[Cost Centers]", {"[Cost Centers].[ID].[ID]"}, {"Cost Centers.ID"}},
            {Cube.AddAndExpandDimensionColumn, "[JE Type]", {"[JE Type].[JE Type].[JE Type]"}, {"JE Type.JE Type"}},
            {Cube.AddAndExpandDimensionColumn, "[Posting Status]", {"[Posting Status].[Posted].[Posted]"}, {"Posting Status.Posted"}},
            {Cube.AddAndExpandDimensionColumn, "[Related to sales]", {"[Related to sales].[Relatedto Sales].[Relatedto Sales]"}, {"Related to sales.Relatedto Sales"}},
            {Cube.AddAndExpandDimensionColumn, "[Scenarios]", {"[Scenarios].[Scenario Key].[Scenario Key]"}, {"Scenarios.Scenario Key"}},
            {Cube.AddAndExpandDimensionColumn, "[Time]", {"[Time].[Date].[Date]", "[Time].[Month].[Month]", "[Time].[Quarter].[Quarter]", "[Time].[Year].[Year]"}, {"Time.Date", "Time.Month", "Time.Quarter", "Time.Year"}},
            {Cube.AddMeasureColumn, "Amount", "[Measures].[Amount]"}
        }),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Items",{{"Amount", Currency.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "IncomeStatementAmount", each if [Chart Of Accounts.Level 02] = "Revenue"
then [Amount]
else if [Chart Of Accounts.Level 02] = "Expenses"
then -[Amount]
else [Amount]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"IncomeStatementAmount", Currency.Type}, {"Time.Date", type date}})
in
    #"Changed Type1"

And the New row which i want to  add has this values :

1. Chart Of Accounts.Level 02 = "Cash Basis Rev"
2. YTD = CALCULATE(SUM(GeneralLedger[Amount]),
   GeneralLedger[Scenarios.Scenario Key] = "Actual",
   GeneralLedger[JE Type.JE Type] IN { "CI", "NR" },
   GeneralLedger[Related to sales.Relatedto Sales] ="1",
   GeneralLedger[AccountType.Account Type] IN { "Cash", "Bank", "NotesReceivable" }
   )
3. Budget = 
   CALCULATE(SUM(GeneralLedger[Amount]),
   GeneralLedger[Scenarios.Scenario Key] = "Budget",
   GeneralLedger[Chart Of Accounts.Level 02] = "Revenues"
   )
4. Variance = CALCULATE(SUM(GeneralLedger[Amount]),
   GeneralLedger[Scenarios.Scenario Key] = "Budget",
   GeneralLedger[Chart Of Accounts.Level 02] = "Revenues"
   ) - 
   CALCULATE(SUM(GeneralLedger[Amount]),
   GeneralLedger[Scenarios.Scenario Key] = "Actual",
   GeneralLedger[JE Type.JE Type] IN { "CI", "NR" },
   GeneralLedger[Related to sales.Relatedto Sales] ="1",
   GeneralLedger[AccountType.Account Type] IN { "Cash", "Bank", "NotesReceivable" }
   )
5. Time.Year = 2018
6. And all other column = Null

[v-juanli-msft · Accepted solution]

Hi @Abdalmoamen

Calculated columns or measures would not show in query editor, so we can’t use them to create columns.

An alternative way is to add custom column replacing the calculated columns/measures, then use the values from these columns to add rows.

For example, calculating sum of a column can be achieved by this formula in query editor

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Sum(Table.SelectRows(#"Changed Type", each [Scenarios.Scenario Key]="Actual")[Amount]))

As for adding rows to exisiting columns, we can use these functions.

Table.InsertRows

list-insertrange

list-union

However, since it needs to create custom columns to replace calculated columns or measures, it would be complex. 

Best Regards

Maggie