Next filtered column

[Anonymous]

I have a matrix with following data

* Row = Product (for example banana, ananas, ...)

* Column = Country (Italy, France, Austria, Germany)

* Value = SalesAmount

I need a new coluymn (measure) calculating the difference of SalesAmount of the filtered columns (country) for example Italy, France, Austria only

i.e. Italy vs. France, and then France vs. Austria

It has to do with calculation with previous and next columns

tks

[Anonymous]

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

[az38]

Hi @Anonymous 

if you show an example of your desired output it could be easier to solve, but from the first sight it looks impossible

do not hesitate to give a kudo to useful posts and mark solutions as solution