Next filtered column

Hi Anonymous ,

In my customers table I added a new column to calculate the SeqNr based on CountryRegion.

SeqNr = RANKX ( ALL ( Customer ), Customer[CountryRegion] ,,asc,Dense)

Now use the following variance measure to show the difference between the columns.
The PreviousColumn make sure that the variance is calculated between the selected columns.

Variance = 
var CurrentColumn = max('Customer'[SeqNr])
var PreviousColumn = CALCULATE( max('Customer'[SeqNr]), ALLSELECTED('Customer'), 'Customer'[SeqNr]<CurrentColumn) 
var MinimumColumn = calculate(min('Customer'[SeqNr]), ALLSELECTED('Customer')) -- just to supress values in the first column
var PreviousValue = 
[Sales Amount]
-
calculate( [Sales Amount],
ALLEXCEPT('Product', 'Product'[Brand]), 
ALL('Customer'[SeqNr] , Customer[CountryRegion]), 
'Customer'[SeqNr] = PreviousColumn ) 
var Result = PreviousValue
return
IF( CurrentColumn > MinimumColumn, Result)

Result is like: 

If you want to have a different sequence, just change the SeqNr calculation and use the SeqNr in the matrix as well. 

Hope this helps

Jan 

if this is a solution for you, don't forget to mark it as such. thanks