Forum Discussion
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 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.
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
4 Replies
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
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 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?
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