Forum Discussion
Measures with Sum in Matrix Rows
Hi all -
I have a Matrix visual showing inventory liquidation data. The rows come from a pre-aggregated fact table. Filtering works fine for 1:1 relationships (stocked month to inventory), but breaks when I add dimensions like Inventory Types or Departments to the row-level fact table. How can I properly filter on these dimensions without breaking the matrix relationships? Here is a sample of the Row fact table:
| Stocked Month | Inventory Stocked | Remaining Inventory |
| 1/31/2021 | 63175 | 2663 |
| 2/28/2021 | 56727 | 2306 |
| 3/31/2021 | 77792 | 3234 |
Here is a sample of the Columns and Values table:
| ID | Sold Date | Sold Date Formatted | Last_Date_Of_Month |
| 1 | 1/21/2021 | 2021-1 | 1/31/2021 |
| 2 | 2/5/2021 | 2021-2 | 1/31/2021 |
| 37 | 1/14/2021 | 2021-1 | 1/31/2021 |
| 103 | 1/15/2021 | 2021-1 | 1/31/2021 |
| 132 | 1/15/2021 | 2021-1 | 1/31/2021 |
| 166 | 1/15/2021 | 2021-1 | 1/31/2021 |
| 89 | 1/19/2021 | 2021-1 | 1/31/2021 |
| 123 | 1/19/2021 | 2021-1 | 1/31/2021 |
| 163 | 1/19/2021 | 2021-1 | 1/31/2021 |
| 175 | 1/19/2021 | 2021-1 | 1/31/2021 |
| 184 | 1/19/2021 | 2021-1 | 1/31/2021 |
| 12 | 1/20/2021 | 2021-1 | 1/31/2021 |
| 118 | 1/20/2021 | 2021-1 | 1/31/2021 |
| 170 | 1/20/2021 | 2021-1 | 1/31/2021 |
| 7 | 1/21/2021 | 2021-1 | 1/31/2021 |
| 18 | 2/9/2021 | 2021-2 | 2/28/2021 |
| 30 | 2/9/2021 | 2021-2 | 2/28/2021 |
| 147 | 2/9/2021 | 2021-2 | 2/28/2021 |
| 86 | 3/16/2021 | 2021-3 | 3/31/2021 |
Here is a sample of the current output. I only want to filter on dimensions. I do not need to display them.
Hi matt_campbell ,
Please find attached snap and PBIX file.
If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
6 Replies
How is your pre-aggregated fact table linked to row-level fact table?
Also, could you explain how the matrix visual works (how each cell is calculated)? That might help others figure out how to build the solution.Thank you for the reply. The tables are linked by Last_Date_Of_Month. I have updated the original post which I left out that column. The matrix cells are the Count Sold divided by Inventory Stocked for the row. The cells can also show Count. Here is a view of count.
Hi matt_campbell ,
Thank you for reaching out to the Microsoft Community Forum.
You are expecting to filter properly without displaying those dimensions in the visual.
Please follow below steps.
1. Created 3 tables, InventoryType, InventoryFact and Department with sample data.
2. Created Measure "Inventory Stocked Filtered" with below DAX.
Inventory Stocked Filtered =
CALCULATE(
SUM('InventoryFact'[Inventory Stocked]),
TREATAS(VALUES(InventoryType[Inventory Type]),InventoryFact[Inventory Type]),
TREATAS(VALUES('Department'[Department]), 'InventoryFact'[Department])
)3. Created Measure "Remaining Inventory Filtered" with below DAX.
Remaining Inventory Filtered =
CALCULATE(
SUM('InventoryFact'[Remaining Inventory]),
TREATAS(VALUES('InventoryType'[Inventory Type]), InventoryFact[Inventory Type]),
TREATAS(VALUES(Department[Department]),InventoryFact[Department])
)4. In Matrix visual, Drag 'InventoryFact'[Stocked Month] in Rows, Inventory Stocked Filtered and Remaining Inventory Filtered measures in Values and
InventoryType[Inventory Type] and 'Department'[Department] in slicers.5. Please refer output snap and attached PBIX file for your reference.
If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.This does not accomplish setting multiple rows. I need each row to be Stocked Month, Stocked Inventory, Inventory Remaining. The columns should be Sold Month. Values are Count of Sold. I've attached an updated example.
Hi matt_campbell ,
Please find attached snap and PBIX file.
If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
