How to create column that would aggregate data for each quarter in Power BI Desktop

Question

The problem is that end user wants to Export data to excel from matrix and view all columns in a dataset.

But if I create a measure and throw it into a visual and then "Export Data" - only brings 3 columns.

RunningTotal_QtrLoss = 
    CALCULATE( 
    SUM(fact_Losses[PaymentAmount]),
    FILTER(ALL(fact_Losses[DevQtr]),fact_Losses[DevQtr]<=MAX(fact_Losses[DevQtr])))

So I am trying to avoid using measure in Power BI in order to be able viewing all columns when export to excel.

For that I calculated RunningTotal in SQL. But then when I used it in a matrix - it summarized value. I am not sure how to make in not summarize.

In order to solve that I want to try to create column (not measure) in Power BI, and hopefully it will let me extract raw data with all columns.
--==============================================================

How can I simply create a custom column (from Edit Query) Sum_Qtr that would give me the sum(Premium) for Quarter and Year?

horseyride horseyride · Accepted Answer · 2019-01-09T22:21:18

one way .. put in a total for all instances, then use if to replace most of them with nulls based on adjacent row

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
TotalAmountAdded = Table.AddColumn(Source, "Total Amount",(i) => List.Sum(Table.SelectRows(Source, each ([Year] = i[Year] and [Quarter] = i[Quarter]))[Premium]), type number),
#"Added Index" = Table.AddIndexColumn(TotalAmountAdded, "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Sum_Qtr", each if [Index] = 0 then [Total Amount] else if Source{[Index]}[Quarter]=Source{[Index]-1}[Quarter] and Source{[Index]}[Year]=Source{[Index]-1}[Year] then null else [Total Amount]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Total Amount", "Index"})
in #"Removed Columns"

How to create column that would aggregate data for each quarter in Power BI Desktop

1 Answers