Forum Discussion
divide between two tables
In that case you need the following measure:
Pallett number = SUMX ( 'Product Table', DIVIDE ( [Despatch Quantity], [Pallet Quantity] ) )
Hello PaulDBrown ,
Thank you for your reply.
The 'Pallet' table includes unique values for [Product], so there is no need for another table.
ProductPallet_Quantity
| Product1 | 25 |
| Product2 | 50 |
| Product3 | 25 |
Below you can see the different results between the manual and DAX calculations.
Manual calculation shows 31 total number of pallets.
| Product1 | Product2 | Product3 | |
| 150 | 100 | 75 | |
| 225 | 50 | 25 | |
| 75 | 50 | ||
| 100 | |||
| QtySum | 450 | 150 | 250 |
| PalletQty | 25 | 50 | 25 |
| PalletNum | 18 | 3 | 10 |
DAX calculation shows 8.5 total number of pallets, matching to your result as well.
| Row Labels | PalletNum |
| Product1 | 4.5 |
| Product2 | 1.5 |
| Product3 | 2.5 |
| Grand Total | 8.5 |
Having said that, focusing on your results table, if I add the 3 rows of the "Despatch Qty by Pallet Qty" column, it gives me the same result as my manual calculation.
Do you know why the grand total of your results table is not equal to the sum of the 3 rows?
Kind regards,
George
In that case you need the following measure:
Pallett number =
SUMX ( 'Product Table', DIVIDE ( [Despatch Quantity], [Pallet Quantity] ) )
Hello PaulDBrown ,
Thank you for your reply.
I can confirm that this measure provides the correct result.
Can I ask for more feedback related to logic behind the correct measure (why this generates the correct result whereas the previous one not)?
Thank you
George
Well, DIVIDE does at it says. So the division is within the filter context and over the totals.
SUMX, on the other hand, performs the calculation (in this case the DIVIDE) within the filter context (so row by row) and then sums the result of each calculation to deliver the total. Make sense?