cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors