Forum Discussion

Power_It_Up's avatar
Power_It_Up
Helper II
2 years ago
Solved

Find % difference between two columns in matrix table

Hi all,   Below Matrix table shows Month headers (text format) and some values for each.   I am wanting to create two row calcs to show the difference between two months and the % difference:-  ...
  • Alex87's avatar
    2 years ago

    1. Make sure your format is Date for the column "Month"

    2. Add a new column MonthNb

     

     

     

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMTJR0lQwMDpVgdiKARTNASIWYMV2gEFIwFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Value = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Value", Int64.Type}}),
        #"Inserted Month" = Table.AddColumn(#"Changed Type", "MonthNb", each Date.Month([Month]), Int64.Type)
    in
        #"Inserted Month"

     

     

     

     

    3. Close Power Query and change the format of the column Month

     

    4. Add the DAX measures:

     

     

     

    Diff MoM = 
    VAR _CurrentValue = SUM(YourTableName[Value])
    VAR _vTable = 
    OFFSET(
        -1,
        ALL(
            YourTableName[Month],
            YourTableName[Value],
            YourTableName[MonthNb]
        ),
        ORDERBY(YourTableName[Month], ASC),
    )
    
    VAR _Selection = 
    SELECTCOLUMNS( _vTable, YourTableName[Value])
    
    VAR _PreviousValue = SUMX(_Selection, YourTableName[Value])
    VAR _Difference = IF(_PreviousValue <> BLANK() ,_PreviousValue-_CurrentValue)
    
    RETURN 
    _Difference
    
    ============================
    %Diff MoM = 
    VAR _CurrentValue = SUM(YourTableName[Value])
    VAR _vTable = 
    OFFSET(
        -1,
        ALL(
            YourTableName[Month],
            YourTableName[Value],
            YourTableName[MonthNb]
        ),
        ORDERBY(YourTableName[Month], ASC),
    )
    
    VAR _Selection = 
    SELECTCOLUMNS( _vTable, YourTableName[Value])
    
    VAR _PreviousValue = SUMX(_Selection, YourTableName[Value])
    VAR _Difference = IF(_PreviousValue <> BLANK() ,_PreviousValue-_CurrentValue)
    
    RETURN 
    DIVIDE(_Difference, _PreviousValue)
    
    
    ==================
    SumValue = SUM('YourTableName'[Value])

     

     

     

    5. Use a Matrix column with the column Month on Columns and SumValues, Diff MoM and %Diff MoM

    In the format pane choose Values/ Options/ Switch values to rows ON

     

    Result

    If it answers your query, please mark my reply as the solution. Thanks!