Forum Discussion
T/F Expression on Filtered & Summarized table
I have a matrix that has Account in Rows, Product in Columns, Order Total in Values, and a Slicer for Order Date with the below format.
I need to sum all order totals grouped by account and product and if that total is > X for the date range then display 1 else 0.
Each product may have a different value for X
| Account | Product | Order Total | Order Date |
| Account 1 | Product 1 | 20 | 3/1/23 |
| Account 1 | Product 1 | 33 | 3/3/23 |
| Account 1 | Product 2 | 51 | 3/3/23 |
| Account 1 | Product 3 | 37 | 3/4/23 |
| Account 2 | Product 1 | 59 | 2/28/23 |
| etc. | etc. | etc. | etc. |
The matrix output would look like this:
| Product 1 | Product 2 | Product 3 | |
| Account 1 | 1 | 1 | 0 |
| Account 2 | 1 | 0 | 0 |
| etc. |
I was trying to do something like this:
IF(SELECTEDVALUE('Table'[Product]) = Product 1 && OrderTotal > 50, 1,
IF(SELECTEDVALUE('Table'[Product]) = "Product 2" && OrderTotal > 0, 1, 0))
However, that will bring in all accounts when another slicer is added to filter for salesperson and does not provide the correct T/F output.
2 Replies
I do have a table for X, what you suggest does provide the correct output for relevant columns/rows, however, it also causes my matrix to display all accounts including those outside of the slicer restriction.
Not sure if thats a model issue or a result of the measure. The slicer is on Salesperson which has a relationship 1 to many on sales rep number to my account table, and the account table has a 1 to many on account numbers on orders.
