Aggregation in Matrix Table

[nthomson]

Hello 😊

I am having an issue with totals in a matrix table. I have encountered similar things before and this time looked into suggestions including building a calculated column instead of measure, but because of the underlying data structure and ‘rolling up’ required I can’t see an easy way to do this. 

Many thanks in advance.

Context:

I am trying to calculate the quantity of each part used per month in our open Orders (call this ‘Part qty per month’)

Parts are used in multiple Products, in varying quantities.

Products are ordered by month and vary by Order qty.

Data is available in 3 tables as below.

ORDERS		PRODUCT		PARTS
Product	Date	Qty		Name		Part	Product	Usage per product
A	Mar-24	100		A		1	A	5
B	Mar-24	200		B		1	B	10
A	Apr-24	300		C		2	C	5
B	Apr-24	300		D		2	D	5
						3	A	5
						3	B	5

I can get the results I want at line level by creating 3 measures:

- Sum of qty of Orders per month

- Sum of Qty of Parts

- Sum of Qty of Parts * Qty of Order per month

This solution gives me a correct value at line level, but totals do not aggregate correctly and show much higher values than desired.

Relationships are as below:

Desired table:

(‘Product’ value would be nested under ‘Part’ in matrix table)

Value per month is Part Usage per product, per date.

		Mar-24	Apr-24
Part	Product
1	A	500	1500
1	B	1000	3000
Total		1500	4500

[nthomson]

Hi @Anonymous 

Thanks for taking the time to review and reply. I don't believe that solution is viable, but I've created a PBIX, available Here

This contains the tables I've mentioned above and some sample data.

I've included a table on Page 1 that illustrates the aggregation issue. Looking at column 'March 2024' and 'Part 1' we see the measure 'Total Quantity used'. This is reached by multiplying 'Quantity used in Product' by 'Qty of Order' and is the total I'm trying to calculate at line level and total level.

At line level (i.e Product 1), the results are correct. Part 1 is used:

• 1 time in Product 1. Product 1 has 1000 orders in March. The calculation is (1*1) * 1000 = 1000
• 2 times in Product 2. Product 2 has 5000 orders in March. The calculation is (1*2) * 5000 = 10000
• zero times in Product 3. Product 3 has 1000 orders in March. The calculation is (0*0) * 1000 = 0

However, at subtotal level (i.e Part 1), the 'Total Quantity used' is 21000 for March. The value should be 11000 (10000 + 1000).

Note I had to create the 3 measures in order to link 'Usage' to 'Orders' table, otherwise 'Date' would not display in the matrix table. 

Many thanks

Nick

[Anonymous · Accepted solution]

Hi @nthomson 

Please try this:

Create a new table:

Table = FILTER(CROSSJOIN('USAGE','ORDERS'),'USAGE'[Product Name1]='ORDERS'[Product Name])

Then add a calculate column:

Column = 'Table'[Quantity]*'Table'[Order Quantity]

At last, Create a matrix:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nthomson]

Thank you @Anonymous , this has solved the issue, much appreciated!

[Anonymous]

Hi @nthomson 

Please try this:

I create 3 table at first:

The relationship:

Then add a measure:

Measure = MAX('ORDERS'[Qty])*MAX('PARTS'[Usage per product])

The result is as follow:

If the above one can't help you, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.