Forum Discussion
Inserting Matrix columns that do not affect totals
I have a Matrix that presents the data in the following manner:
| Product Line COOKING | Product Line REFRIGERATION | Total Sale |
01-2022 | $100 | $100 | $200 |
- The Product Line is an attribute of the product being sold.
- The sale amounts are currently using a measure that I created (I think I did this so it would present $0 if there were no sales instead of blank)
Total Sales = if(ISBLANK(SUM('Sales Order Lines'[Sale Amount])),0, sum('Sales Order Lines'[Sale Amount]))
I need to add a new column into the Matrix that is based on a different attribute (the customer), but that new column cannot inflate the total in the last column in some cases.
For example, a COOKING item was sold to a customer considered INDUSTRIAL. The sale amounts would display in both the COOKING & INDUSTRIAL columns, but not affect the totals (as shown below).
| Product Line COOKING | Product Line REFRIGERATION | INDUSTRIAL SALES | Total Sale |
01-2022 | $100 | $100 | $100 | $200 |
Another caveat to this as that INDUSTRIAL is also a product line on the item so there may be some sales where an INDUSTRIAL item was sold to a customer NOT considered INDUSTRIAL. In that case I would want it affect the totals. For example, a sale was made in Feb for an item that is considered INDUSTRIAL.
| Product Line COOKING | Product Line REFRIGERATION | INDUSTRIAL SALES | Total Sale |
01-2022 | $100 | $100 | $100 | $200 |
02-2022 | $100 | $100 | $200 | $400 |
I don’t know if anyone can provide me with some advice on how to address this. I could create a measure for each of the product lines that filters for only what I want to include because these product lines are static, but I was hoping to find a better way.
1 Reply
- lbendlin
Super User
Remember that a measure in a Matrix visual is calculated four times. For the individual cells, for the row subtotal, the column subtotal, and for the Grand Total. You can apply whatever logic you want, including filtering out certain values in the row subtotal (which is what you seem to want in this case).