Forum Discussion

Abdalmoamen's avatar
Abdalmoamen
Frequent Visitor
8 years ago
Solved

Add Custom Row with calculated Measures

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...
Need Help
Show and Tell
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    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

v-juanli-msft's avatar
v-juanli-msft
Community Support
8 years ago

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