Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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