Measures with Sum in Matrix Rows

[matt_campbell]

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.

[v-dineshya]

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.

[matt_campbell]

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. 

[v-dineshya · Accepted solution]

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.

[v-dineshya]

Hi @matt_campbell ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thank you.

[TzuChiao26]

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.

[matt_campbell]

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.