Forum Discussion
divide between two tables
Hello Community,
I would appreciate your help with the below.
I want to understand how I can divide the "Despatch"[Quantity] by the "Pallet"[Pallet_Quantity].
I have built a relationship between them, based on [Product] columns.
I tried SUM(Product[Quantity])/SUM(Pallet[Pallet_Quantity]) but it doesn't work.
| Despatch | ||
| Product | Quantity | Destination |
| Product1 | 150 | LocationA |
| Product1 | 225 | LocationB |
| Product1 | 75 | LocationB |
| Product2 | 100 | LocationC |
| Product2 | 50 | LocationD |
| Product3 | 75 | LocationA |
| Product3 | 25 | LocationC |
| Product3 | 50 | LocationB |
| Product3 | 100 | LocationD |
| Pallet | |
| Product | Pallet_Quantity |
| Product1 | 25 |
| Product2 | 50 |
| Product3 | 25 |
Thank you,
George
In that case you need the following measure:
Pallett number = SUMX ( 'Product Table', DIVIDE ( [Despatch Quantity], [Pallet Quantity] ) )
10 Replies
Can you please upload the screenshot of your model or clear the relation between the tables?
Create the model as follows. The Dim Product table must have unique product values (if the product table has unique product values you can use it as the dimension table instead of creating a new one):
Create the measures:
Despatch Quantity = SUM('Despatch Table'[Quantity])Pallet Quantity = SUM('Product Table'[Pallet_Quantity])Despacth Qty by Pallett Qty = DIVIDE([Despatch Quantity], [Pallet Quantity])Create the table with the field from the Dim Product table and add the measures
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 mh2587 ,
Thank you for your reply. Please see the data model below.
I tried to remove the relationship, it didnt work.
Below oyu can see the different results between the manual and the DAX calculations.
Manual Calculation
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
Row Labels PalletNum Product1 4.5 Product2 1.5 Product3 2.5 Grand Total 8.5 Thank you,
George
