Calculate difference between columns in the matrix

[Kopek]

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)

[Kopek · Accepted solution]

Found a solution.

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

[v-linyulu-msft]

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

[Kopek]

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.

[lbendlin]

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.

[Kopek]

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,..

[lbendlin]

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.

[Kopek]

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

[lbendlin]

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.

[lbendlin]

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

How do you compute the difference between four values?