topic Re: Find % difference between two columns in matrix table in DAX Commands and Tips

Find % difference between two columns in matrix table

Power_It_Up — Mon, 20 May 2024 16:05:10 GMT

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:-

In Tableau, I used a LOOKUP/OFFSET function to achieve this.

I've tried using many functions like PARALLELPERIOD or PREVIOUSMONTH, but they all error as the columns are not DATE driven, but TEXT.

Can someone help me on this?

Many thanks.

Re: Find % difference between two columns in matrix table

Alex87 — Mon, 20 May 2024 16:38:08 GMT

is there any reason you don't want to use DATE format?

Re: Find % difference between two columns in matrix table

Power_It_Up — Mon, 20 May 2024 16:41:50 GMT

Alex87 Hi. Not really. I used the FORMAT option to change the full date to just months, which made them into TEXT.

Re: Find % difference between two columns in matrix table

Alex87 — Mon, 20 May 2024 19:26:24 GMT

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!

Re: Find % difference between two columns in matrix table

Power_It_Up — Fri, 31 May 2024 08:34:28 GMT

Hi Alex87. Firstly, my apologies for not replying to you sooner. Many thanks for your input on this. Apart from the percentages being slightly out (should be -10% and +33%, which I amended), this worked well and it was insightful how you used the OFFSET function. Thanks again. 🙂