Forum Discussion

Brookied's avatar
Brookied
Helper I
9 years ago
Solved

Calculate Cumulative Variance help

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

 

 

 

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    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

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    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