Forum Discussion

nthomson's avatar
nthomson
Frequent Visitor
2 years ago
Solved

Aggregation in Matrix Table

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,...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
nthomson
Frequent Visitor
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    nthomson
    Frequent Visitor
    2 years ago

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