topic Calculate difference between columns in the matrix in DAX Commands and Tips

Calculate difference between columns in the matrix

Kopek — Fri, 01 Nov 2024 10:35:35 GMT

Hi,

I have the following dataset:

DateCategoryValue

01/01/2020	Category 1	100
01/01/2020	Category 1	2
01/01/2020	Category 2	3
01/01/2020	Category 3	56
01/01/2020	Category 3	76
01/01/2020	Category 2	12
01/03/2020	Category 3	56
01/03/2020	Category 4	34
01/03/2020	Category 4	78
01/03/2020	Category 1	78
01/03/2020	Category 2	9
01/01/2021	Category 2	12
01/01/2021	Category 2	5
01/01/2021	Category 1	34
01/01/2021	Category 1	67
01/01/2021	Category 3	18
01/01/2021	Category 3	99

Out of which i create a simple matrix:

Now i want to add 2 additional columns difference and diif% at the end which will count difference in column based on selected categories and if all selected based on all selcted, can ypou help me with dax please ?

e.g
Cat 1 and 2 selected, outcome:

01/01/2020 difference 87 (102-15)
01/01/2021 difference 84 (101-17)

01/03/2020 difference 69 (78-9)

Re: Calculate difference between columns in the matrix

lbendlin — Fri, 01 Nov 2024 12:30:20 GMT

based on selected categories and if all selected based on all selcted

How do you compute the difference between four values?

Re: Calculate difference between columns in the matrix

Kopek — Fri, 01 Nov 2024 12:42:58 GMT

Hi lbendlin - that's actually a fair point...
So let's say we will limit selection to 2 categories only, but how to make it dynamic, as the configuration might be different, one may select Category 1 and 2, other one Category 1 &3 and other one 4&2 and so on... and we want to compute the difference and diff % between selected categories.. hope it makes sense.

Re: Calculate difference between columns in the matrix

lbendlin — Fri, 01 Nov 2024 12:52:34 GMT

I would go with Visual Calculations - they have a concept of PREVIOUS where you can look sideways in a visual and compare to the neighboring values.

Re: Calculate difference between columns in the matrix

Kopek — Fri, 01 Nov 2024 12:57:16 GMT

not sure it will work either as we want to have one column for difference and one for diff%, not category columns split by new columns,..

Re: Calculate difference between columns in the matrix

lbendlin — Fri, 01 Nov 2024 13:03:32 GMT

Correct, you can only abuse the Column Totals once.

You would be looking into creating a custom matrix with a reference table for the desired column names and a measure to do the switching. This becomes very ugly very quickly and is frankly not worth the effort.

Re: Calculate difference between columns in the matrix

Kopek — Fri, 01 Nov 2024 13:28:43 GMT

so.. there is no simple solution to this ?

looks like it should not be that complicated... total is computed somehow, so why we are not ab;eto overwitte it with difference or something like this ?:(

Re: Calculate difference between columns in the matrix

lbendlin — Fri, 01 Nov 2024 13:38:23 GMT

Because as you said - you would have to add the additional measures to each of the columns (categories). You cannot "add columns to a matrix" - that's not a thing. Power BI is not Excel.

Re: Calculate difference between columns in the matrix

Kopek — Fri, 01 Nov 2024 14:43:49 GMT

Found a solution.

In case anyone stucked here is a good starting point: Solved: Difference instead of Total in PowerBI Matrix - Microsoft Fabric Community

Re: Calculate difference between columns in the matrix

Anonymous — Mon, 04 Nov 2024 02:06:19 GMT

Hi,Kopek
Thank you for sharing the challenges you've faced. We're thrilled that you've found a solution and appreciate your openness to exploring others' ideas. This insight is invaluable for community members dealing with similar issues.

Once again, thank you for your efforts.

Wishing you all the best!

Of course, if you have any new discoveries or questions, please feel free to get in touch with us.

Best Regards,

Leroy Lu