Filter matrix based on margin performance

[Anonymous]

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 table looks like this:

Item No	Date	Margin
1	week1	1
1	week2	1
2	week1	1
2	week2	0.5
3	week1	1
3	week2	1.5

I want to create a filter for this matrix to show items with a margin increase/decrease. (include the former week history) 

the ideal result would look like this

margin increase:

Item No\Margin\Date	week1	week2
3	1	1.5

margin decrease:

Item No\Margin\Date	week1	week2
2	1	0.5

Thanks for any help.

[v-deddai1-msft]

Hi @Anonymous ,

It can't be achieved in matrix. Would you please try to pivot column on your table and display values in table visual and then you can filter it by increasing or decreasing:

Then create measure for filtering it by increasing or decreasing:

Measure = SWITCH(TRUE(),MAX('Table'[week2])>MAX('Table'[week1]),0,MAX('Table'[week2])<MAX('Table'[week1]),1,2)

Best Regards,

Dedmon Dai

[Anonymous]

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/...). 

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 No	Date	Margin	margin comparison (success with the linked post)	Filter Column
1	week1	1
1	week2	1
2	week1	1		decrease
2	week2	0.5	decrease	decrease
3	week1	1		increase
3	week2	1.5	increase	increase

Is there any way we can do so?

[v-deddai1-msft · Accepted solution]

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]

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.