cancel
Showing results for
Did you mean:
Anonymous
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

tks

2 REPLIES 2
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 columnvar PreviousValue = [Sales Amount]-calculate( [Sales Amount],ALLEXCEPT('Product', 'Product'[Brand]), ALL('Customer'[SeqNr] , Customer[CountryRegion]), 'Customer'[SeqNr] = PreviousColumn ) var Result = PreviousValuereturnIF( 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

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

Announcements

#### Power BI September 2023 Update

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

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### 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!

#### 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