Forum Discussion
Power_It_Up
2 years agoHelper II
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:- ...
- 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!
Alex87
2 years agoSolution Sage
is there any reason you don't want to use DATE format?
- Power_It_Up2 years agoHelper II
Alex87 Hi. Not really. I used the FORMAT option to change the full date to just months, which made them into TEXT.