Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter matrix based on margin performance

Hi, I have a problem with the matrix filter issue. Now I have a matrix table looks like this: ItemNo\Margin\Date week 1 week2 1 1 1 2 1 0.5 3 1 1.5 the original data tab...
Need Help
Tips and Tricks
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi Anonymous,

     

    I suggest you use the following dax to create a calculated column to rank the temNo:

     

    rank =

CALCULATE (

    COUNTROWS ( 'Table' ),

    FILTER (

        ALL ( 'Table' ),

        'Table'[tem No] = EARLIER ( 'Table'[tem No] )

            && 'Table'[Index] <= EARLIER ( 'Table'[Index] )

    )

)

     

    Then use the following dax to created the filter column:

     

    Column 2 =

VAR b = 'Table'[Margin]

VAR a =

    CALCULATE (

        MAX ( 'Table'[Margin] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[tem No] = EARLIER ( 'Table'[tem No] )

                && 'Table'[rank]

                    = EARLIER ( 'Table'[rank] ) - 1

        )

    )

VAR c =

    CALCULATE (

        MAX ( 'Table'[Margin] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[tem No] = EARLIER ( 'Table'[tem No] )

                && 'Table'[rank]

                    = EARLIER ( 'Table'[rank] ) + 1

        )

    )

RETURN

    IF (

        'Table'[rank] = 1,

        SWITCH ( TRUE (), b > c, "decrease", b < c, "increase", BLANK () ),

        SWITCH ( TRUE (), b < a, "decrease", b > a, "increase", BLANK () )

    )

     

    For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EU2VJv_n_n9PpDgia5AoYmYBcJOEMmtSudf5fMKybWzpgQ?e=CVHJel

     

     

    Best Regards,

    Dedmon Dai

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Dedmon,

 

Thanks for your reply. I'm afraid this would not be the solution for me. Since I have a relatively large data set here (10 files in a folder and each has ~4000 rows), it is risky to transpose. Still, in the sample data, I showed only 2 weeks' history which is easy to use the switch function with hardcoded week number column, while in reality, I need to keep the flexibility comparing multiple (not a specific number) weeks. 

 

As for now, I successfully use a DAX column to compare the margin increase/decrease, refers to this post (https://community.powerbi.com/t5/Desktop/Conditional-formatting-on-Rows-based-on-previous-value/m-p/724176). 

 

What I can see for the next steps are:

-after comparing and marked margin increase/decrease, create a column with DAX -- if the item was marked with increase/decrease for week2, then we can mark week 1 as increase/decrease for the same item number. 

-In this case, I can apply this column to a filter to successfully filter the matrix.

 

the ideal back table would look like this:

Item NoDateMarginmargin comparison (success with the linked post)Filter Column
1week11  
1week21  
2week11 decrease
2week20.5decreasedecrease
3week11 increase
3week21.5increaseincrease

Is there any way we can do so?

v-deddai1-msft's avatar
v-deddai1-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous,

 

I suggest you use the following dax to create a calculated column to rank the temNo:

 

rank =

CALCULATE (

    COUNTROWS ( 'Table' ),

    FILTER (

        ALL ( 'Table' ),

        'Table'[tem No] = EARLIER ( 'Table'[tem No] )

            && 'Table'[Index] <= EARLIER ( 'Table'[Index] )

    )

)

 

Then use the following dax to created the filter column:

 

Column 2 =

VAR b = 'Table'[Margin]

VAR a =

    CALCULATE (

        MAX ( 'Table'[Margin] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[tem No] = EARLIER ( 'Table'[tem No] )

                && 'Table'[rank]

                    = EARLIER ( 'Table'[rank] ) - 1

        )

    )

VAR c =

    CALCULATE (

        MAX ( 'Table'[Margin] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[tem No] = EARLIER ( 'Table'[tem No] )

                && 'Table'[rank]

                    = EARLIER ( 'Table'[rank] ) + 1

        )

    )

RETURN

    IF (

        'Table'[rank] = 1,

        SWITCH ( TRUE (), b > c, "decrease", b < c, "increase", BLANK () ),

        SWITCH ( TRUE (), b < a, "decrease", b > a, "increase", BLANK () )

    )

 

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EU2VJv_n_n9PpDgia5AoYmYBcJOEMmtSudf5fMKybWzpgQ?e=CVHJel

 

 

Best Regards,

Dedmon Dai

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi there,

     

    I just tried to practice this solution to my situation and it didn't work.

    I believe it didn't make sense for the EARLIER() usage. Since the item number is not ascending or descending in my situation. 

    Also, as I mentioned in the former post. I applied the attached method to create a column to show an increase or decrease.

    Could you help me find a solution based on my description before?

    For instance, I only want to get an idea of how to transfer the "increase" or "decrease" message to the same item in the previous week. 

     

    Thanks.