Showing results for 
Search instead for 
Did you mean: 
Not applicable

Next filtered column

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






Responsive Resident
Responsive Resident

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
IF( CurrentColumn > MinimumColumn, Result)


Result is like: 


2020-01-18 15_35_28-Window.jpg

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

2020-01-18 15_48_45-Window.jpg

Hope this helps



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

Community Champion
Community Champion

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

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

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors