Calculate Cumulative Variance help

[Brookied]

Hi Guys,

I have a sticky (for me) dax to work out.  My budget sheet (See below grab. 

I need to knwpo how in PowerBI to work out the **bleep** Variance  Grab one is the data, Grab 2 below is the simple formula 

In my Desktop i have a table with the Total(budgeted) Actual Inc but cannot get the Dax for Cumluantve Variance. 

Any help at all would be bloody fantastic

[v-yulgu-msft · Accepted solution]

Hi @Brookied,

Suppose the data source imported into Power BI desktop looks like below:

First, you should change its structure in Query Editor mode.

let
    Source = Excel.Workbook(File.Contents("C:\Users\v-yulgu\Desktop\Sample Data.xlsx"), null, true),
    #"budget table_Sheet" = Source{[Item="budget table",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"budget table_Sheet",{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Column1]), "Column1", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Attribute"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1)
in
    #"Added Index"

Based on the new table, then, create calculated column using below formula:

Variance =
'budget table'[Actual] - 'budget table'[Total]

Cumluantve Variance =
CALCULATE (
    SUM ( 'budget table'[Variance] ),
    FILTER (
        'budget table',
        'budget table'[Index] <= EARLIER ( 'budget table'[Index] )
    )
)

Best regards,
Yuliana Gu